MrExcel.com - Your One Stop for Excel Tips & Solutions

Macro needed to select an "incomplete" range

Posted by Jennifer Johnson on November 04, 2001 5:56 AM

Hi ~
I work with a range of cells that varies each day (maybe A1 to D500 one day, and A1 to F475 the next day, etc). I have figured out how to select the range if all the cells in the range are occupied, but sometimes even that is not the case. For example, if my range today is from A1 to D500, sometimes cell D500 is empty (but A500, B500, and C500 have data in them). Or maybe A1 is blank or any other random cell in the range is blank. I just need a macro to select the entire range even if one of the cells within the range is empty, especially if it is a "corner cell" like D500 or A1, keeping in mind that the range is slightly different every day. Can this be done? Thank you if anyone can help me.
~ Jennifer J

Posted by David McKay on November 04, 2001 6:24 AM


Here's one way (I think this method originated from Bob Umlas) :-

Dim LastRow As Long, lastCol As Long
Dim lastCell As Range
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
lastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set lastCell = Cells(LastRow, lastCol)
Range(Range("A1"), lastCell).Select


Posted by Jennifer Johnson on November 04, 2001 6:43 AM

That's better but still a problem

Thank you David, but one problem, what this does is select everything I have on my spreadsheet as part of the same range, no matter how many rows and columns separate my actual range from other cells that are unrelated but occupied somewhere else on the same sheet.

This looks like a step in the right direction, but what I'd like is for the macro to stop trying to select a larger range when it encounters its first totally empty row and first totally empty column. I hope this is a clear way to describe what I am hoping for.

Jennifer J


Posted by David McKay on November 04, 2001 6:54 AM

Re: That's better but still a problem

Check whether this does what you need :-

Range("A1").CurrentRegion.Select


Posted by Jennifer Johnson on November 04, 2001 7:37 AM

Thank you!!!

Wow, that does just what I'm looking for, and it's so short of a macro I wouldn't have guessed. Thank you David!!! :)

Jennifer J

Check whether this does what you need :- Range("A1").CurrentRegion.Select : Thank you David, but one problem, what this does is select everything I have on my spreadsheet as part of the same range, no matter how many rows and columns separate my actual range from other cells that are unrelated but occupied somewhere else on the same sheet. : This looks like a step in the right direction, but what I'd like is for the macro to stop trying to select a larger range when it encounters its first totally empty row and first totally empty column. I hope this is a clear way to describe what I am hoping for. : Jennifer J :