Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Quickly Write Multidimensional Array to Excel Range

I explained how to write multidimensional array to an Excel range using for next loop from earlier post. But there is much quicker way to do that without for loop.

So lets put below data to a multidimensional array call "PopulationDensity" and then write it back to a new sheet using this quick method.
























So here is the code to do that.

Dim WS As Worksheet

Set WS = Worksheets("Sheet4")

Dim PopulationDensity() As Variant

PopulationDensity = WS.Range("A2:D16")

Dim WS_New As Worksheet

Set WS_New = Worksheets.Add

WS_New.Range(Range("A1"), Range("A1").Offset(UBound(PopulationDensity, 1) - 1, _
UBound(PopulationDensity, 2) - 1)).Value = PopulationDensity

Erase PopulationDensity

And this is the result you will get.



















Index of this array starts from 1. If index of your array start from 0 then you need to change below line



WS_New.Range(Range("A1"), Range("A1").Offset(UBound(PopulationDensity, 1) - 1, _
UBound(PopulationDensity, 2) - 1)).Value = PopulationDensity

as follows

WS_New.Range(Range("A1"), Range("A1").Offset(UBound(PopulationDensity, 1) , _
UBound(PopulationDensity, 2) )).Value = PopulationDensity

Contact Form

Name

Email *

Message *

Popular Posts