Excel VBA Delete Row based on column value

Question

I wanted to write a Excel Macro that goes though K1--->K(lastrow) and looks for the value "OptedOut", and if it finds that value then it deletes that row. I appreciate the help guys. The only part that is wrong is the For Each C part, because I don't understand arrays, and possibly "c.Value = "OptedOut" Then Rows(c).Delete" kinda pulled that out of my ass.

Thanks all!

Sub DuplicateDelete()
Sheets("ALL CLIENTS").Range("A1:J10000").Copy Destination:=Sheets("ClientsAndEmailsThatAreOK").Range("A1:J10000")

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
    MsgBox LastRow
End With


'Dim c As Range
For Each c In Range(Range(Cells("K1"), Cells(LastRow, "K")))
    If c.Value = "OptedOut" Then Rows(c).Delete
Next c


End Sub

Is this code suppose to wrok with sheet "ALL CLIENTS" or "ClientsAndEmailsThatAreOK" ? — Shai Rado

Shai Rado Shai Rado · Accepted Answer · 2017-09-07T04:28:14

Loop backwards when deleting rows (or other objects).

Also, instead of using ActiveSheet try to fully qualify your Worksheet object, such as Sheets("ClientsAndEmailsThatAreOK").

Try the code below, explanation inside the code's comments:

Option Explicit

Sub DuplicateDelete()

Dim C As Range
Dim i As Long, LastRow As Long

Sheets("ALL CLIENTS").Range("A1:J10000").Copy Destination:=Sheets("ClientsAndEmailsThatAreOK").Range("A1:J10000")

' I'm assuming you want to work with sheet "ClientsAndEmailsThatAreOK" (if not then switch it)
With Sheets("ClientsAndEmailsThatAreOK")
    LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
    MsgBox LastRow

    ' always loop backwards when deleting rows
    For i = LastRow To 1 Step -1
        If .Range("K" & i).Value2 = "OptedOut" Then .Rows(i).Delete
    Next i
End With

End Sub

Excel VBA Delete Row based on column value

1 Answers