Cut and Delete Multiple Rows - Criteria Based

[revolvermac]

Good morning all,

I have a macro that works great for cutting/deleting specific rows, however you have to run it multiple times if there are more than one row that meets the criteria. I want to adjust it so it cuts ALL rows meeting the chosen criteria with one pass. Here's what I have so far:

Sub ClearTerminated()
'
' ClearTerminated Macro
'

'
Dim i As Variant
Dim endrow As Integer
Dim MasterList As Worksheet, Inactive As Worksheet

Set MasterList = ActiveWorkbook.Sheets("Master List")
Set Inactive = ActiveWorkbook.Sheets("Inactive")

endrow = MasterList.Range("A" & MasterList.Rows.Count).End(xlUp).Row

For i = 3 To endrow
If MasterList.Cells(i, "B").Value = "TERM" Then
MasterList.Cells(i, "B").EntireRow.Cut Destination:=Inactive.Range("A" & Inactive.Rows.Count).End(xlUp).Offset(1)
MasterList.Cells(i, "B").EntireRow.Delete
End If
Next

End Sub

 

[Stridhan]

try reverse the loop For i = endrow to 3 step -1

 

[revolvermac]

Hi Stridhan,

Thanks for taking the time to respond. I modified the code as per your recommendation, but unfortunately to no result. This change prevents even a single row from being cut/deleted from the "Master List" table. :/

 

[MickG]

This worked for me !!!
NB:- The word "TERM" must be uppercase in sheet.

[COLOR="Navy"]Sub[/COLOR] MG11Jun48
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Endrow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] MasterList [COLOR="Navy"]As[/COLOR] Worksheet, Inactive [COLOR="Navy"]As[/COLOR] Worksheet


[COLOR="Navy"]Set[/COLOR] MasterList = ActiveWorkbook.Sheets("Master List")
[COLOR="Navy"]Set[/COLOR] Inactive = ActiveWorkbook.Sheets("Inactive")


Endrow = MasterList.Range("A" & MasterList.Rows.Count).End(xlUp).Row


[COLOR="Navy"]For[/COLOR] i = Endrow To 3 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]If[/COLOR] MasterList.Cells(i, "B").Value = "TERM" [COLOR="Navy"]Then[/COLOR]
        MasterList.Cells(i, "B").EntireRow.Cut Destination:=Inactive.Range("A" & Inactive.Rows.Count).End(xlUp).Offset(1)
        MasterList.Cells(i, "B").EntireRow.Delete
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[revolvermac]

Bingo - works like a charm now. Thank you both!