Last row used

abenitez77 · #1 04-04-2019, 08:52 PM

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

Code:

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 (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:

Code:

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)

Pecoflyer · #2 04-04-2019, 11:40 PM

May I remind you of post #3 from this thread ? https://www.msofficeforums.com/excel...tml#post140134

abenitez77 · #3 04-05-2019, 04:39 AM

Quote:

Originally Posted by Pecoflyer

May I remind you of post #3 from this thread ? https://www.msofficeforums.com/excel...tml#post140134

fixed it...thanks

NoSparks · #4 04-05-2019, 07:05 AM

Perhaps something along the lines of

Code:

Sub testing()

    Dim lastRow As Long, lastColumn As Long, desiredRow As Long
    Dim ws As Worksheet, rRange As Range, myarray2 As Variant

Set ws = ActiveSheet  '<<< alter to suit

lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).Column

desiredRow = Range(Cells(1, 1), Cells(lastRow - 1, lastColumn)).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

Set rRange = Range(Cells(1, 1), Cells(desiredRow, lastColumn))
myarray2 = rRange
    
    MsgBox "First dimension (rows)" & vbLf & _
           "   lower bound is   " & LBound(myarray2, 1) & vbLf & _
           "   upper bound is   " & UBound(myarray2, 1) & vbLf & vbLf & _
           "Second dimension (columns)" & vbLf & _
           "   lower bound is   " & LBound(myarray2, 2) & vbLf & _
           "   upper bound is   " & UBound(myarray2, 2)
End Sub

abenitez77 · #5 04-05-2019, 07:33 AM

Thanks, I am using this which is what you have but just added "After:=Cells(18000,5)" It is working fine for me:

Code:

RowLast = Columns(5).Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, After:=Cells(18000, 5)).Row