Finding the last row of a contiguous range

[RalphH]

Is there a better way of finding the last row of a contiguous range
than:

Selection.End(xlDown).Select

 

[Gary Keramidas]

one way, as an example

dim lastrow as long
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

range("A1:A" & lastrow).copy

 

[Gary Keramidas]

sorry, forgot something:

dim lastrow as long
dim ws as worksheet

Set ws = Worksheets("Sheet1")
lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

range("A1:A" & lastrow).copy

 

[Jim Cone]

Sub WhatsTheNumber()
Dim lngR As Long
lngR = Selection.Rows(Selection.Rows.Count).Row
MsgBox lngR
End Sub
'- or -
Sub SelectRow()
Selection.Rows(Selection.Rows.Count).Select
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"RalphH" <[email protected]>
wrote in message
Is there a better way of finding the last row of a contiguous range
than:

Selection.End(xlDown).Select

 

[elachowitz]

Oh yes there is...

Dim LastRow as Long

LastRow = yourSheetName.Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

....Its my favorite... Enjoy!

 

[Tom Ogilvy]

There are a myriad of situations that would make your suggestion return the
wrong answer for the question asked. Besides that, what makes you think it
is better.

--
Regards,
Tom Ogilvy

Oh yes there is...

Dim LastRow as Long

LastRow = yourSheetName.Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

...Its my favorite... Enjoy!

Is there a better way of finding the last row of a contiguous range
than:

Selection.End(xlDown).Select

 

[elachowitz]

Well the original statement was for [Selection.End(xlDown).Select]
which does not fully encapsulate connecting without a break (which is
what I read the question to be)

The reason I like it… the absolute last row of a sheet will allow for
looping statements to evaluate all of your ranges/cells in question…

In addition, you will not need to consistently save your sheet using
my statement as you would with lets say
[Cells.SpecialCells(xlCellTypeLastCell).Row] or some other methods.
Just personal preference, nothing more.

 

[elachowitz]

Well the original statement was for [Selection.End(xlDown).Select]
which does not fully encapsulate connecting without a break (which is
what I read the question to be)

The reason I like it… the absolute last row of a sheet will allow for
looping statements to evaluate all of your ranges/cells in question…

In addition, you will not need to consistently save your sheet using
my statement as you would with lets say
[Cells.SpecialCells(xlCellTypeLastCell).Row] or some other methods.
Just personal preference, nothing more.