Iceskull
07-14-2017, 07:18 AM
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
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