Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
End(xlDown)
Hi Fellow Excel users,
Has anyone come across the following problem before and do they have an answer? End(xlDown) is a useful shortcut method, however in a macro similar to this below, if the cells below contains only one record or no records, then the count is 1048575 in Excel 2007 or 50000 in excel 2003. Sub CntCells() ans = Range(Range("A1"), Range("A1").End(xlDown)).Count MsgBox ans End Sub Similarly Range("A1").End(xlDown).Offset(1, 0).Select fails because Range("A1").End(xlDown).Select will select the last row Has anyone any thoughts or alternatives? Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
End(xlDown)
Try the other way (xlUp). To get the last row filled in Column A
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row If this post helps click Yes --------------- Jacob Skaria "Normek" wrote: Hi Fellow Excel users, Has anyone come across the following problem before and do they have an answer? End(xlDown) is a useful shortcut method, however in a macro similar to this below, if the cells below contains only one record or no records, then the count is 1048575 in Excel 2007 or 50000 in excel 2003. Sub CntCells() ans = Range(Range("A1"), Range("A1").End(xlDown)).Count MsgBox ans End Sub Similarly Range("A1").End(xlDown).Offset(1, 0).Select fails because Range("A1").End(xlDown).Select will select the last row Has anyone any thoughts or alternatives? Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
End(xlDown)
Hi
Another option, if you for some reason need to use xlDown: If Range("A2")="" then LastRow=2 Else LastRow=Range("A1").end(xlDown).row End if Hopes this helps. .... Per "Normek" skrev i meddelelsen ... Hi Fellow Excel users, Has anyone come across the following problem before and do they have an answer? End(xlDown) is a useful shortcut method, however in a macro similar to this below, if the cells below contains only one record or no records, then the count is 1048575 in Excel 2007 or 50000 in excel 2003. Sub CntCells() ans = Range(Range("A1"), Range("A1").End(xlDown)).Count MsgBox ans End Sub Similarly Range("A1").End(xlDown).Offset(1, 0).Select fails because Range("A1").End(xlDown).Select will select the last row Has anyone any thoughts or alternatives? Thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
End(xlDown)
Thanks Jacob Skaria,
That was very helpful and answered the second part of my question here is what I came up with: Sub SelectCell() lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Range("b1").Offset(lngLastRow, 0).Select End Sub The first part was indirectly answered by smartin in a previous post today called " How to count number of rows with data?" Here is what I came up with: Sub CntCells() ans = Application.WorksheetFunction.CountA(Range(Range(" B2"), Range("B2").End(xlDown))) MsgBox ans End Sub Thanks to both of you! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
End(xlDown)
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
Range("b1").Offset(lngLastRow, 0).Select You might find this interesting... this single line of code will accomplish the same thing your two lines of code do... ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Offset(1).Select The outcome from this part of the statement... ActiveSheet.Cells(Rows.Count, "B").End(xlUp) is a Range (actually, a single cell... the last cell in Column B with data in it), so you can use Offset to move down one row and then select it. -- Rick (MVP - Excel) "Normek" wrote in message ... Thanks Jacob Skaria, That was very helpful and answered the second part of my question here is what I came up with: Sub SelectCell() lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Range("b1").Offset(lngLastRow, 0).Select End Sub The first part was indirectly answered by smartin in a previous post today called " How to count number of rows with data?" Here is what I came up with: Sub CntCells() ans = Application.WorksheetFunction.CountA(Range(Range(" B2"), Range("B2").End(xlDown))) MsgBox ans End Sub Thanks to both of you! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
End(xlDown)
Excellent! Thank you Rick, I'm glad I made this querie, the response has been
fantastic! "Rick Rothstein" wrote: lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Range("b1").Offset(lngLastRow, 0).Select You might find this interesting... this single line of code will accomplish the same thing your two lines of code do... ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Offset(1).Select The outcome from this part of the statement... ActiveSheet.Cells(Rows.Count, "B").End(xlUp) is a Range (actually, a single cell... the last cell in Column B with data in it), so you can use Offset to move down one row and then select it. -- Rick (MVP - Excel) "Normek" wrote in message ... Thanks Jacob Skaria, That was very helpful and answered the second part of my question here is what I came up with: Sub SelectCell() lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Range("b1").Offset(lngLastRow, 0).Select End Sub The first part was indirectly answered by smartin in a previous post today called " How to count number of rows with data?" Here is what I came up with: Sub CntCells() ans = Application.WorksheetFunction.CountA(Range(Range(" B2"), Range("B2").End(xlDown))) MsgBox ans End Sub Thanks to both of you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
End(xlDown) function | Excel Programming | |||
A1:B(xlDown)? | Excel Programming | |||
End(xlDown) not working? | Excel Discussion (Misc queries) | |||
End(xldown) not always going to last cell? | Excel Programming | |||
repeated end(xldown) | Excel Programming |