Finding the last row of a contiguous range
 

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

Selection.End(xlDown).Select

Finding the last row of a contiguous range
 

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

Finding the last row of a contiguous range
 

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

Finding the last row of a contiguous range
 

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

Finding the last row of a contiguous range
 

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

Finding the last row of a contiguous range
 

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

Finding the last row of a contiguous range
 

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.

Finding the last row of a contiguous range
 

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.