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, Commented Sep 7, 2017 at 4:24

YowE3K · Accepted Answer · 2017-09-07 04:50:07Z

3

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

edited Sep 7, 2017 at 4:50

YowE3K

24k7 gold badges28 silver badges45 bronze badges

answered Sep 7, 2017 at 4:28

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Delete Row based on column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related