Dynamically finding a range in Excell with VBA

Posted by Jamey on May 15, 2001 11:04 AM

How would i dynamically find a range in excel? I have 22 worksheets(all in own workbooks) with different amounts of data(i.e. more/less rows of info). I need a way to loop thru the cells till it finds a blank one then assigns the top cell to the one just before the blank cell to a variable...

Any help would be much appreciated.

Posted by Dave Hawley on May 15, 2001 3:32 PM

Hi Jamey

Excel VBA can handle this quite easily, eg;

Sub FindRange()
Range("A1", Range("A1").End(xlDown)).Copy
End Sub

This procedure will copy the data in Column A from A1 to the first blank cell. If you want to go to the very last cell in the Column (including any blanks) use:

Sub FindRange()
Range("A1", Range("A65536").End(xlup)).Copy
End Sub

Dave

OzGrid Business Applications