Get Last Row of Data

[abenitez77]

Hello,
I am using this line to get the last row and column used:

Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow
Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn

It is working but I have a sheet where the last row is row 18488. The problem is that between that row and the next row up is row 98. Everything in between is empty. The last row (A1) (in row 18488) has a text "Please do not enter data beyond this row. The price entry limit is 20,000 rows." I want my last row to be row 98. How do I exclude the text in row 18488?

I am getting the last row so that I can use it as a range and then save it to an array so I can loop thru:

Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow
Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn

Set rRange = Range(Cells(1, 1), Cells(LastRow, LastCol))

      ' Copy the Range to an Array
      myarray2 = rRange
            
      For r = 3 To UBound(myarray2)

 

[Joe4]

One way to get the last row number:

Dim lr as Long
lr = ws.Cells(18488,"A").End(xlUp).Row

If you then wanted to select the whole row, you can use:

Rows(lr).Select

 

[abenitez77]

Thanks Joe, that was very usefull

 

[Joe4]

You are welcome.