Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Join non-contiguous ranges into one range via named range? | Excel Discussion (Misc queries) | |||
Finding the longest contiguous 1-d array of 0's | Excel Discussion (Misc queries) | |||
Finding non contiguous positive values | Excel Worksheet Functions | |||
Finding last column in non-contiguous range | Excel Worksheet Functions | |||
Non-contiguous Range limit? | Excel Programming |