Pass values from a range object to an array

[Bill_Kro]

I have some code where the user selects cells. (the location and number of cells selected will vary)
The code creates a range object based on the selected cells and assigns the variable name: Selected_Cells
I want to pass the values from the Selected_Cells range object to an array but don't know how to do this.
Thanks,

 

[Domenic]

First declare a variable as a Variant...

    Dim v As Variant

Then you can transfer the values from your Range object to the variable like this...

    v = Selected_Cells.Value

Note, since the values are being transferred from the worksheet, the variable is assigned a 1-based two dimensional array, even if the Range object consists of one column or row. So to refer to the first item of the first row...

 v(1,1)

Hope this helps!

 

[Bill_Kro]

Domenic,

Thanks, I think the last part about it being a 1-based two dimensional array is my problem.

I was going to take the array and use it as the Criteria in an autofilter.

If I assign the create the array

Code:
v = array("Apple", "Pear", "Cherry")

then when I use it in the autofilter
Code:
Criteria1:=v(),

Then the table filters properly

When I selected the cells with Apple, Pear and Cherry as their values
and then assign them to the range object Selected_Cells
Code:
v = Selected_Cells.value
...
Criteria1:=v()

Then the table only filters on the first value - Apple

When I look in the Locals window, the array values are different.
In the first case when I directly defined the array contents, the Type appears to be Variant(0 to 2) and the details are listed as Variant/String
When I tried to define the array from the range object, the Type says Variant/Variant(1 to 3, 1 to 1) and the details are listed as Variant (1 to 1)

Is there a way around this problem?

 

[mikerickson]

You could loop the values into a 1 dimensional 0-based array

Dim arrValues() as String
Dim oneCell as Range, i as Long

ReDim arrValues(0 to Selected_Cells.Count - 1)

i = 0
For Each oneCell in Selected_Cells.Cells
    arrValue(i) = CStr(oneCell.Value)
    i = i + 1
Next oneCell

 

[Bill_Kro]

Mike,
Thank you so much! It worked perfectly. It was driving me crazy that I couldn't figure it out. I'm so thankful.
Bill

 

[Domenic]

It looks like v has been assigned a N-Row by 1-Column array, in which case you would need to first transpose it to one dimensional array...

v = Application.Transpose(v)

Then you could assign it to Criteria1...

Criteria1:=v

 

[igold]

This post is very similar to your post here https://www.mrexcel.com/forum/excel-questions/1089446-pass-values-range-object-array.html

Perhaps you do not realize that when this kind of InputBox comes up the user does not enter information into the box itself but actually selects the range of cells manually as if the input box was not there. When you click "Ok" on the InputBox the range selected is put into an array. Again, you do not enter info into the InputBox.