CODEWITHSUNDEEP
arraysvbaexcelrange
Michael_448
Michael_448

Reputation: 11

Sending a dynamic range to a function

Newbie here, so please bear with me. I'm just not getting something about properly putting a dynamic column of values into an array.

The following lines of code work properly for me:

Set RangeD = Sheets("Data").Range("D2:D15")
unique2chan = UniqueItems(RangeD, False)

RangeD is the range I will pass to the function, called "UniqueItems". "unique2chan" is the array of values that will be returned by UniqueItems.

The assumption above is that the values always run from D2 to D15. But for these data, the range's length can vary on each run. (All values will be consecutive in the column, no spaces.)

Here's my question: How do I put a changing number of values into an array that I can pass to my function? I added a counter that tells me how many rows are filled in the column, so if there are 12 values in the column (plus the header), I have a counter that will read 13. How can I use that number to define a range of all the values (minus the header) that I can put into an array to pass to my function?

Any help is appreciated. I've tried a bunch of things I've found on the net, but everyone seems to have a different approach, and I haven't found one that worked for me.

Upvotes: 0

Views: 63

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

You can use Resize():

Set RangeD = Sheets("Data").Range("D2").Resize(numRows,1)
unique2chan = UniqueItems(RangeD, False)

Upvotes: 1

Related Questions