Avatar of gisvpn
gisvpn
Flag for United States of America asked on

Delete Rows via VBA

Hello,

I have a worksheet which is called data and in column Z has a string of either Done, Ready or Hold.

I would like to delete all rows which have the value Done or Hold in it?

Is this an easy thing to add to my VBA project?

Thanks,

GISVPN
Microsoft Excel

Avatar of undefined
Last Comment
gisvpn

8/22/2022 - Mon
zorvek (Kevin Jones)

Use this macro:

Public Sub DeleteRows()

    Dim Row As Long
   
    With ThisWorkbook.Sheets("Sheet1")
        For Row = .UsedRange.Row + .UsedRange.Rows.Count - 1 To .UsedRange.Row Step 1
            If .Cells(Row, "Z").Value = "Done" Or .Cells(Row, "Z").Value = "Hold" Then
                .Rows(Row).Delete
            End If
        Next Row
    End With

End Sub

Kevin
gisvpn

ASKER
Hi Kevin,

I have been having a problem with the above. I cant seem to get it to work?

Looking at the code it does not seem to run this part of the code at all.

 For Row = .UsedRange.Row + .UsedRange.Rows.Count - 1 To .UsedRange.Row Step 1
            If .Cells(Row, "Z").Value = "Done" Or .Cells(Row, "Z").Value = "Hold" Then
                .Rows(Row).Delete
            End If

Is there anyway I can check what the code is doing at each step. I have a feeling its something to do with

For Row = .UsedRange.Row + .UsedRange.Rows.Count - 1 To .UsedRange.Row Step 1


:)

Thanks
ASKER CERTIFIED SOLUTION
zorvek (Kevin Jones)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
gisvpn

ASKER
Hi Kevin,

Was the fix removing the - in this bit of code ? "Step -1"
Your help has saved me hundreds of hours of internet surfing.
fblack61