1

Dears,

I was thinking the below codes would bring me to the last non-bank row in a column:

Sub commandtryanderror()
    Range("a1").End(xlDown).Select
End Sub

However, I found if there is only the cell A1 has content, the above codes would bring me to the end row in excel, that is row number 1048576 but if there are contents in cell A2 and A3, the above codes will bring me to A3, and that is what I want, can you tell me why and how should I correct the program? Thanks.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
Eric
  • 69
  • 7

1 Answers1

1

The following code works. It checks if your macro would send you to the bottom of the sheet. If that is the case it will send you to cell A1.

Sub find_last_cell()

If Range("A1").End(xlDown) = Range("A1048576") _
    Then
    Range("A1").Select
Else
    Range("A1").End(xlDown).Select
End If

End Sub

Do note, if there is a gap in your data, it will not pass the data. If this is a problem you can convert the macro to search from the bottom up.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Rick
  • 53
  • 8
  • Yes, i tried and it works, but could you tell me why such phenomena happen in my original codes? I always think the command end(Xldown) will bring me to the last non-blank row, why it go to the row 1048576 instead, but it works normal when cell "a2" has contents.... – Eric Aug 03 '21 at 16:25
  • 1
    I have just found this link: https://stackoverflow.com/questions/32192372/why-does-range-endxldown-row-return-1048576 It seems that is the answer...Thanks. – Eric Aug 04 '21 at 15:11
  • I can confirm. If you want to see the effect of ```end(x1[Direction]```, use [ctrl] + [direction arrow]. If there are no cells with data between the selected cell and the edge of the document, you will jump to the edge. – Rick Aug 06 '21 at 06:29