MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to select a range of cells using macro

Posted by Brad on December 21, 2000 8:42 AM

I am trying to select some cells using a macro. I have data in column 2 and there are varying amounts of blank cells between the data. I want to be able to select a range starting with an entry then going down until the cell before the next entry (entry and all blank cells below it). I am doing this to format the cells. I want to border each entry and the blank cells below it. Basically I want it to look like a pivot table. All data to the right of this column will be bordered the same so I just need to figure out how to do this column.


Posted by JohnLat on December 21, 2000 11:01 AM

Brad, this command selects from the current active cell
to the last non-blank cell in a column. Is that what you want?

Range(ActiveCell, (Cells(65536, ActiveCell.Column).End(xlUp))).Select

Posted by Brad on December 21, 2000 12:47 PM

Actually, I want to select from active cell to the last blank cell before the next non-blank. Then format borders around this range. Then go on to the next range and do the same.


Posted by Tony Scala on December 22, 2000 6:39 AM

Same code just change .end(xlUp))) to .end(xldown))) this will move to last used cell. TS