Creating an Array from a Variable Range in VBA

Asked May 02 '17 at 14:22

Active May 02 '17 at 14:22

Viewed 1,034 times

I'm looking for a way to create an array from a variable range in VBA. I've seen a similar posting but I can not find the way to adjust it to a variable range.

What I have is a Sheet with variable columns and I want store these into an array. So far I have the following code, but it is giving an error when trying to assign the dynamic range (HCRng)

Sub TestColumnNames()
Dim ArrayColumns As Variant
Dim HCRng As Range
HCRng = Sheets("HC").Range("A1", Selection.End(xlToRight))
ArrayColumns = HCRng.Value
End Sub

Any ideas?

edited May 23 '17 at 12:34

Community

asked May 02 '17 at 14:22

Selrac

2,203
9
41
84

6

To assign to any object variable (including a Range variable), you need to use Set. So "Set HCRng = Sheets(..." – Rich Holton May 02 '17 at 14:24
2

also this will error if the selection is on a different sheet than `HC`, just an FYI. – Scott Craner May 02 '17 at 14:25
7

You may want to change that line to: `set HCRng = Sheets("HC").Range("A1", Sheets("HC").Range("A1").End(xlToRight))` – Scott Craner May 02 '17 at 14:26
Thanks Scott. That works!! – Selrac May 02 '17 at 14:27
2

To populate `ArrayColumns` you can cut out the middleman: `ArrayColumns = Sheets("HC").Range("A1", Selection.End(xlToRight)).Value`. Unless you plan to do other things with `HCRng`, it isn't needed. – John Coleman May 02 '17 at 15:00

Creating an Array from a Variable Range in VBA

0 Answers0