Delete Unwanted Rows

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