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

Selection.End(xlDown).Select

one way, as an example

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

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


--


Gary


"RalphH" wrote in message
ups.com...
Is there a better way of finding the last row of a contiguous range
than:

Selection.End(xlDown).Select

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


--


Gary


"RalphH" wrote in message
ups.com...
Is there a better way of finding the last row of a contiguous range
than:

Selection.End(xlDown).Select

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"
wrote in message
Is there a better way of finding the last row of a contiguous range
than:

Selection.End(xlDown).Select

Oh yes there is...

Dim LastRow as Long

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

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


On 4 Oct 2006 09:01:31 -0700, "RalphH" wrote:

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

Selection.End(xlDown).Select

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

"elachowitz" wrote in message
...
Oh yes there is...

Dim LastRow as Long

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

...Its my favorite... Enjoy!


On 4 Oct 2006 09:01:31 -0700, "RalphH" wrote:

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

Selection.End(xlDown).Select

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.

On Thu, 5 Oct 2006 21:19:47 -0400, "Tom Ogilvy"
wrote:

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.

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.

On Thu, 5 Oct 2006 21:19:47 -0400, "Tom Ogilvy"
wrote:

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.