find and delete rows macro

[clayton ledford]

Hello,

I have created a macro to re-format a report i receive in excel. I'm having
a problem adding the following instruction.

I need to use the macro to find and select the last five rows, then delete
those rows. This is a problem because the report length varies each day when
i receive the report.

Even if there is one to find and select just the last row. I can repeat 5
times.

Clay

 

[Mike]

This might work for you
Sub deleterows()
Dim rng As Range
Dim i As Long

Set rng = ActiveSheet.Range(Cells(1, "A"), _
Cells(Rows.Count, "A").End(xlUp))

With rng
For i = .Rows.Count To .Rows.Count - 4 Step -1
.Cells(i).EntireRow.Delete
Next i
End With
End Sub

 

[Mike H]

Maybe

lastrow = UsedRange.Rows.Count
Rows(lastrow).Offset(-4).EntireRow.Resize(5,
ActiveSheet.Columns.Count).ClearContents

Mike

 

[Don Guillett]

Try this. Change column to suit. To delete simply change
..select
to
..delete

Sub selectlastfive()
mc = 6 ' "f"
'Cells(Rows.Count, mc).End(xlUp).Resize(-5).Select
'since resize doesn't work for negative

Cells(Rows.Count, mc).End(xlUp).Offset(-4).Resize(5).Select
End Sub

 

[clayton ledford]

This one worked perfectly. Genius. Thanks guys.