Range("X65536").End(xlUp).Row is not working

Question

Hi I need help with this one line in my VBA:

lastrow = wsTarget.Range("X65536").End(xlUp).Row
MsgBox lastrow

wsTarget is the worksheet i am working on, I want to know what is the last row of data. By looking at the sheet, the last line should be 9; however, it keeps returning 1 to me. Am I doing something wrong?

Why not try to "select" so that you can see exactly which cell is getting referenced: `lastrow = Range("X65536").End(xlUp).Select` — Roberto, Aug 16 '13 at 00:35
It *is* working. It's just not doing what you think it ought. There are several methods for obtaining the last used row in a given range. This one is not always reliable (nor is `.End(xlDown).Row`. — David Zemens, Aug 16 '13 at 00:37
Or do `MsgBox wsTarget.Range("X65536").End(xlUp).Address` to see what cell the End(xlUp) is finding. — chuff, Aug 16 '13 at 00:39
If you want the last cell that contains data in the worksheet, try `wsTarget.Cells.SpecialCells(xlLastCell)`. Or may be you have data more than 65536 rows at column X. sometimes when data has been pasted or inserted a few times, the "Last Cell" increases in row/column which seemingly has no value but Excel thinks it has. See if the row of Last cell is more than 65536. — PatricK, Aug 16 '13 at 00:48
possible duplicate of [Last not empty cell in row; Excel VBA](http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba) — Ken White, Aug 16 '13 at 01:57

score 0 · Answer 1 · answered Aug 16 '13 at 01:50

0

It's a little bit longer to type, but perhaps the Range.Find method will work for you:

lastrow = wsTarget.Columns("X").Find("*", wsTarget.Range("X1"), xlValues, SearchDirection:=xlPrevious).Row
MsgBox lastrow

answered Aug 16 '13 at 01:50

tigeravatar

26,199
5
30
38

score 0 · Answer 2 · answered Aug 16 '13 at 02:35

In future, try avoid determine the last row by moving up from a defined cell. Especially 65536 which is the max row for Excel 2003 can handle (old code!). We should make codes to be version independent!

You will be good with below code for all future versions of office for what you are trying to find:

' Start going up from next row of last cell
lastrow = wsTarget.Cells.SpecialCells(xlLastCell).row + 1
lastrow = wsTarget.Range("X" & lastrow).End(xlUp).Row

score 0 · Answer 3 · answered Aug 16 '13 at 02:47

Based on "the last row of data" not necessarily coinciding with ColumnX maybe the following courtesy would suit:

Sub Last_Real_Populated_Row()
    ActiveCell.SpecialCells(xlLastCell).Select
    LastR = ActiveCell.Row
    LastC = ActiveCell.Column
    LastRealC = 1
    For Counter = LastR To 1 Step -1
        Range(Cells(Counter, LastC), Cells(Counter, LastC)).Select
        Selection.End(xlToLeft).Select
        If Not IsEmpty(ActiveCell.Value) Then
            LastRealR = ActiveCell.Row
            Exit For
        End If
    Next
End Sub

Range("X65536").End(xlUp).Row is not working

3 Answers3