Populate Variable Array from Range - VBA

[moranrd]

What is the VBA code to populate a variable array using the ActiveCell.CurrentRegion

The current region can change either by adding rows or columns


Thanks for your help

[Kenneth Hobs]

Sub ken()
Dim a() As Variant, v As Variant
a() = ActiveCell.CurrentRegion
For Each v In a()
MsgBox v
Next v
End Sub

[moranrd]

Of course that is the right answer. That's the way I worded the question. And thank you for the quick response.

What I MEANT to say was that I wanted to fill a two-dimensional variable array with the active cell's current region. e.g. the current region may have 35 rows and 4 columns OR another current region may have 10 rows and two columns.

My apologies... I would still like to know how to do this

[Kenneth Hobs]

You lost me. Are you redefining what a CurrentRegion means?

Try posting an example workbook to get the best help.

[moranrd]

You lost me. Are you redefining what a CurrentRegion means?

Try posting an example workbook to get the best help.
What I am doing is reading down the nth column of an array for a specified string. If it matches, I want a partial row to be copied to a 2nd array.

Then when the loop is complete, I will take the 2nd array and post it to another sheet.

Example, Range("A1:G1000) will be read and be the source the 1st array, with column C containing a string to match. If there is a match, then that item of the array will be added to the 2nd array. Looping down to Range("C1000), the next action will be to write then second array (the matching items) to another sheet. If there are 100 matches the second array would Array2(100,7).

I already know that I can copy each "match" to another sheet one row at a time. I am looking for something faster and more elegant. And... I already know that application.ScreenUpdating = False improves the speed.