MrExcel Publishing
Your One Stop for Excel Tips & Solutions

selecting a variable range

Posted by Krista on June 18, 2001 1:46 PM

I am trying to use a macro to select an unknown range of cells. The macro searches for the last line in a column and identifies the number of the last row, defined as variable "L".

How can I use this variable in the range command, along with my column variable, col?

I have gotten the command Cells to work with these variables for single cells, but not a range of them. I have tried: Range(col & 1: col & L) etc with no luck.

What can I do to correct this or what am I doing wrong?

Posted by Russell on June 18, 2001 1:55 PM

You're almost there:

As long as the range is rectangular in form, you can use the Cells property within Range to select your range. Just use the upper-left and the lower-right (or the upper-right and lower-left). If there's just one column worth, just use the upper and lower (etc., etc.).


your range = Range(Cells(BeginRow, BeginCol),Cells(L, col))

Hope this helps,


Posted by Krista on June 18, 2001 2:03 PM

Oh,yeah, I would like to put this in R1C1 format, perhaps this is part of the problem? Will I have to initiate a "Cells" loop to accomplish my goal?

Posted by Krista on June 18, 2001 2:05 PM

Russel, you're wonderful!