Pass values from a range object to an array

Bill_Kro

New Member
Joined
Oct 28, 2017
Messages
14
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,
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,323
Office Version
  1. 365
Platform
  1. Windows
First declare a variable as a Variant...

Code:
    Dim v As Variant
Then you can transfer the values from your Range object to the variable like this...


Code:
    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...

Code:
 v(1,1)

Hope this helps!
 

Bill_Kro

New Member
Joined
Oct 28, 2017
Messages
14
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,220
You could loop the values into a 1 dimensional 0-based array

Code:
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

New Member
Joined
Oct 28, 2017
Messages
14

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,323
Office Version
  1. 365
Platform
  1. Windows
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...

Code:
v = Application.Transpose(v)

Then you could assign it to Criteria1...

Code:
Criteria1:=v
 
Last edited:

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,917
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,875
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top