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.