End(xlDown)

[Normek]

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

 

[Jacob Skaria]

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

 

[Per Jessen]

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]

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!

 

[Rick Rothstein]

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.

 

[Normek]

Excellent! Thank you Rick, I'm glad I made this querie, the response has been
fantastic!