Delete a row if a cell contains a specific value

[BLRITCHIE]

Hi,
I have a worksheet that contains 18 columns and a variable number of rows. I need to be able, at the push of a button, delete rows, only if they contain a specific value (in this case, 'cancelled' in Q:Q). I have several other command buttons linked to macros in this sheet, but do not have any idea where to start with the code for this application.
Any help greatly appreciated.

[Paul]

Hi BLRITCHIE,

The simple, maybe not the most elegant way, would be to loop through all of the used cells in column Q, and delete those rows where 'cancelled' appears. The following macro attached to a command button should work:

Private Sub CommandButton1_Click()
    Dim i As Long
    For i = [Q65536].End(xlUp).Row To 2 Step -1
        If UCase(Cells(i, 17).Value) = "CANCELLED" Then
            Cells(i, 17).EntireRow.Delete Shift:=xlUp
        End If
    Next i
End Sub

[BLRITCHIE]

Thanks - I didn't expect a reply so quickly.
Have used the code you supplied and works a treat - bit clunky as you said but does exactly what I need. Thanks again.

[Paul]

To stop the screen from twitching while the macro runs, you could add

Application.ScreenUpdating = False

at the beginning, and set it back to True at the end.

[BLRITCHIE]

I've added this to some of the other existing functions on the sheet and the whole thing looks a lot better now. Cheers.