find and delete rows macro
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 |
find and delete rows macro
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 |
find and delete rows macro
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 |
find and delete rows macro
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 |
find and delete rows macro
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 |
All times are GMT +1. The time now is 08:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com