Checkbox to Delete Row

[Iceskull]

Hi there,

i made a UserForm with some CheckBoxes, if i select the wanted CheckBoxes and press the Button, it deletes the Rows that stand for this Boxes.

All works how i want it to work. But there should be a smarter way of doing this and i want to learn this way.

In total i would have something arround 50 Checkboxes, is there a better way than my code bellow?

Private Sub CommandButton1_Click()


Dim MyCol As String
Dim i As Integer


If CheckBox1.Value = True Then


    For i = 2 To Range("S" & "65536").End(xlUp).Row Step 1
      If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), "1") > 0 Then
        Range("S" & i).EntireRow.Delete
    End If


    Next i
End If






If CheckBox2.Value = True Then


    For i = 2 To Range("S" & "65536").End(xlUp).Row Step 1
      If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), "2") > 0 Then
        Range("S" & i).EntireRow.Delete
    End If


    Next i
End If






If CheckBox3.Value = True Then


    For i = 2 To Range("S" & "65536").End(xlUp).Row Step 1
      If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), "3") > 0 Then
        Range("S" & i).EntireRow.Delete
    End If


    Next i
End If






End Sub

that just 3 CheckBoxes but this would be to long if i add 50 of them

Thanks for your Help

Best Regards

[mikerickson]

Deleting rows working from the top down often gives disappointing results. This works from the bottom up.
This will loop through checkboxes adjusting the search value as it goes.

For j = 1 to 3
    If Me.Controls("CheckBox" & j).Value then
        For i = Range("S" & "65536").End(xlUp).Row To 2 Step -1 
            If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), j) > 0 Then 
                Range("S" & i).EntireRow.Delete 
            End If 
        Next i
    End If
Next i

[Iceskull]

Deleting rows working from the top down often gives disappointing results. This works from the bottom up.
This will loop through checkboxes adjusting the search value as it goes.

For j = 1 to 3
    If Me.Controls("CheckBox" & j).Value then
        For i = Range("S" & "65536").End(xlUp).Row To 2 Step -1 
            If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), j) > 0 Then 
                Range("S" & i).EntireRow.Delete 
            End If 
        Next i
    End If
Next i

Ohh thats alot smarter, Thank you this works perfectly.
I think the wrong way, but i learned from this.