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

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

"clayton ledford" wrote:

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

Maybe

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

Mike

"clayton ledford" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"clayton ledford" wrote in
message ...
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

This one worked perfectly. Genius. Thanks guys.

"Mike" wrote:

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

"clayton ledford" wrote:

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