Delete Unwanted Rows

**adam2308** · 09-14-2012, 08:19 AM

On the attached example I want to add some code to the Command Button that when pressed deletes the rows where column J has the text "No Shops".

Any ideas on how to write the code?

Cheers.

**mike7952** · 09-14-2012, 08:33 AM

Here you go

 Dim ptr As Long, LastRow As Long
 
 LastRow = Cells(Rows.CountLarge, "j").End(xlUp).Row
 
 With Application
    .ScreenUpdating = False
    For ptr = LastRow To 2 Step -1
       If Cells(ptr, "j") = "No Shops" Then
           Cells(ptr, "j").EntireRow.Delete
       End If
    Next
    .ScreenUpdating = True
 End With

**nested.if1@gmail.com** · 09-14-2012, 08:33 AM

Try the below code...

Private Sub CommandButton1_Click()
n = Range("J65536").End(xlUp).Row
For i = 1 To n
    If Cells(i, 10).Value = "No Shops" Then
        Cells(i, 10).EntireRow.Delete
        i = i - 1
    End If
Next i
End Sub