Delete the last 3 rows with VBA

[doriannjeshi]

Hi,
I need to delete the last 3 rows with VBA

 

[Skyybot]

Paste in Sheet code module of the Sheet you want to affect.

Sub DeleteLast3()
Dim lRow As Integer, i As Integer
lRow = Me.UsedRange.Rows.Count
For i = lRow To lRow - 2 Step -1
    Me.Rows(i).EntireRow.Delete
Next i
End Sub

 

[doriannjeshi]

blockA	Attr	blockb	NR1	NR2
12:00:00 AM	2	29	8	0
12:00:00 AM	2	OMO	1	0
12:00:00 AM	2	IDR	1	0
12:00:00 AM	2	ICP	2	1
12:00:00 AM	2	IMS	1	0
12:00:00 AM	2	IVL	1	0
12:00:00 AM	2	ITO	2	0
12:00:00 AM	2	ITG	1	1
12:00:00 AM	2	IFG	1	0
12:00:00 AM	2	37	9	0
12:00:00 AM	2	ISH	1	0
			28	2
			48	4
			1

 

[Skyybot]

Code has to be pasted into Code module of Sheet. Otherwise change Me to Activesheet

 

[doriannjeshi]

yes the updated code:


Sub DeleteLast3()
Dim lRow As Integer, i As Integer
lRow = ActiveSheet.UsedRange.Rows.Count
For i = lRow To lRow - 2 Step -1
ActiveSheet.Rows(i).EntireRow.Delete
Next i
End Sub


the message

 

[Joe4]

For your rows with data, is there always one column that will be populated, like column A?
If so, you can do something like this (no loops needed!)

Sub MyDelete()

    Dim lr As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Delete last three rows
    If lr >= 3 Then Rows(lr - 2 & ":" & lr).Delete

End Sub

 

[MARK858]

Just another option (although I would think post 6 is more efficient), again assumes the last row with data is column A

Sub DeleterowAgain()
    Dim LstCell As Range

    Set LstCell = Cells(Rows.Count, "A").End(xlUp)

    With LstCell
        If .Row >= 3 Then .Offset(-2).Resize(3).EntireRow.Delete
    End With

End Sub

Btw

yes the updated code:


Sub DeleteLast3()
Dim lRow As Integer, i As Integer

Dim lRow As Integer, i As Integer

Should be

Dim lRow As Long, i As Long

 

[jolivanes]

Change reference for Sheet1 as required and "Select" to "Delete" when happy that it does what you want.

Sub Or_So_Maybe()
    Sheets("Sheet1").Cells(Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row, 1).Offset(-2).Resize(3).EntireRow.Select
End Sub

 

[MARK858]

Doesn't really need the 2nd

Sheets("Sheet1").Cells(

Sub Or_So_Maybe2()
   Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Offset(-2).Resize(3).EntireRow.Select
End Sub

does the same as Find is already finding a cell in the row

 

[jolivanes]

Hi Mark.
Habit.
There seems to be more problems with not having it where it should be than having it where it does not need to be.
A habit where it does not hurt even if it is not needed.
Thanks for pointing it out though Mark. Appreciated.