Select Last in Range

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Hi,

I am writing a macro to copy a list of data from one spreadsheet to another.
The list includes blank rows as separators. I want to select the entire
list minus the blanks. I don't know how many rows it has or how many blanks
there are. I would have preferred to use Selection.End(xlDown) but the
blanks stop the selection early.

My macro goes to the cell named "Start" and offsets one row down. This I
know is the top of the list. I then select 1000 rows below that, because I
know the list would never be that long. Then I have
Selection.SpecialCells(xlCellTypeConstants, 23).Select, which selects all
the non-empty cells in the previous selection.

Now I want to select the cell in the last row of that selection so that I
can set a variable equal to the row number and use that instead of 1000.
How do I select the last cell in an active range?

Bill
 
Bill

To set last cell in column A

Sub select_last()
Dim rng As Range
Set rng = Cells(Rows.Count, 1).End(xlUp)
rng.Select
End Sub

To select from active cell to last cell in any column

Sub selectrange1()
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Select
End Sub

Gord Dibben Excel MVP
 
Back
Top