VBA- Using the value of Dim set as a Long Variable to make reference to a specific row on another sp-CodePudding

I've been stumped this VBA code for a while and would be grateful for help from anyone.

I would like to use the last cell reference from column A of one of my spreadsheets as a basis to delete rows that are beneath that number from another spreadsheet that's within the same workbook.

Currently I have the following VBA written as a message to delete rows that are that are beneath the total. Please note that I had to add 5 to the total count as the headers have a 5 row difference between them.

My code is written below.

Dim lRow As Long


'Find the last non-blank cell in column A(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Row


Sheets("EIB (w formula)").Select

MsgBox "Last row of LN load: " & lRow & vbNewLine & _
        "Delete all rows beneath A" & lRow   5

CodePudding user response：

Just clear from lrow 5 down to the very last row:

With Sheets("EIB (w formula)")
   .Rows(lRow   5 & ":" & .Rows.Count).Clear
End With