Thanks Stewart
ReDim...of course.

I managed to delete an important line in the code I pasted- here it is again.

I still cannot get the HderRows variable (in the 2nd part of the code) to
return a row or cell value however...I am not sure what to apply in terms of
var data-type and/or cell property (.Value?) for the loop to return a row or
cell for each HderRows variable. All my attempts are returning errors or
unwanted values.

M.

'Defines Number of Header Rows
NoHderRows = 1

Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(CntMon + 5, 0).Select
(MISSING) NoHderRows = NoHderRows + 1
Loop
NoHderRows = NoHderRows - 1

'Defines HeaderRows
Range("B3").Select
For j = 1 To NoHderRows
HderRows(j) = ActiveCell.Select
ActiveCell.Offset(CntMon + 5, 0).Select
Next j


"Datasort" wrote:

You have 2 problems:

First is that the do while loop might possible expand past the array value.

Secondly,

you can use Dim HderRows() As String to set an unknown array size but you
must at some point use the redim to reset the array size BEFORE you start
using the array

Good Luck,

Stewart Rogers

"Mourinho" wrote:

I have this macro to assign rows with data headers to the variable HderRows
- I want to return HderRows as a Row number or Cell though can't get it to
work..missing something here about Object variables or something.
- Not sure why Dim HderRows () doesn't allow empty parenthesis. Understand
this should give a variable array (though 15 is a reasonable max anyway).

Thanks

M.

Dim HderRows(15) As String
NoHderRows = 1
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(CntMon + 5, 0).Select
Loop
NoHderRows = NoHderRows - 1

'Defines HeaderRows
Range("B3").Select
For j = 1 To NoHderRows
HderRows(j) = ActiveCell.Value
ActiveCell.Offset(CntMon + 5, 0).Select
Next j