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

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)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gisvpn

ASKER

Hi Kevin,

Was the fix removing the - in this bit of code ? "Step -1"