Hello all,
I am trying to create a macro that will help me clear certain rows from a large spreadsheet.
I wish to delete rows with the text "FULL REFUND" in column K. I have managed to create the following macro to enable me to do this.
Quote:
Sub DeleteRefund()
'
' DeleteRefund Macro
'
Firstrow = 2
Lastrow = 10000
Range("k1:k10000").Select
Do
If ActiveCell = "FULL REFUND" Then
ActiveCell.EntireRow.Delete shift:=xlUp
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(1, 0)) And IsEmpty(ActiveCell.Offset(2, 0)) And IsEmpty(ActiveCell.Offset(3, 0)) And IsEmpty(ActiveCell.Offset(4, 0)) And IsEmpty(ActiveCell.Offset(5, 0)) And IsEmpty(ActiveCell.Offset(6, 0)) And IsEmpty(ActiveCell.Offset(7, 0)) And IsEmpty(ActiveCell.Offset(8, 0)) And IsEmpty(ActiveCell.Offset(9, 0)) And IsEmpty(ActiveCell.Offset(10, 0))
' The above line runs the hotel removal macro until it finds at least 10 rows in column k empty
'
End Sub
|
Evidently this isn't a very clean macro and it doesn't also delete the row above "FULL REFUND".
My questions to anyone who can help are:
1) Can you give me an example of how to delete the "FULL REFUND" rows and 1 row above
2) A line of code to end the macro when it finds empty rows at the bottom of the sheet.
Many thanks in advance.
Regards,
Jake
dBforums
>
VBA method for deleting row + 1 row above
11-10-11, 07:33
Linear Mode
