How to assign an array of values to a range in Excel VBA

Question

I am creating column headers for a bunch of columns. I would like to do them in one like below - but I couldn't get the syntax right. Can anyone help?

Range("AB1:AE1").Value = ("UnitPriceUSD","TotalCostUSD","UnitPrice", "TotalCost")

mattboy · Accepted Answer · 2012-10-04 08:29:33Z

6

Try this.

Dim arr As Variant
arr = Array("UnitPriceUSD", "TotalCostUSD", "UnitPrice", "TotalCost")

Range("AB1:AE1").Value = arr

Or even simpler:

Range("AB1:AE1").Value = Array("UnitPriceUSD", "TotalCostUSD", "UnitPrice", "TotalCost")

answered Oct 4, 2012 at 8:29

mattboy

2,9085 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Siraj Samsudeen Over a year ago

do I need to declare any array first? I just tried this and it works - Range("AB1:AE1").Value = Array("UnitPriceUSD","TotalCostUSD","UnitPrice", "TotalCost"). Someone else posted this as an answer but deleted it.

mattboy Over a year ago

Yes that seems to be enough, I always overcomplicate things.

Graham Monkman Over a year ago

Works for assigning across columns in a given row. But Range("A1:A2") = Array("a","b") assigns "a" to both A1 and A2.I assume the Array needs to be 2-dimensional, but Array only appears to support creation of 1D arrays.

Marc Meketon Over a year ago

@GrahamMonkman. Look at stackoverflow.com/q/67014070/1955013 for the Ted Williams comment. [{"a", "b"; "c", "d"}] will create a 2-dimensional array

Collectives™ on Stack Overflow

How to assign an array of values to a range in Excel VBA

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related