Because the code deletes data ensure you backup your workbook before you test
this code.
When deleting data from a worksheet I like to work on the safe side and give
the user and out in case they select the wrong thing. I have attached the
code to a button rather than a combo box click event so that it is 2 distinct
operations to select and then run the delete code. I have also put a msgbox
prompt in to confirm the action.
Also I don't know what type of data is in the combo box. Numeric or text.
Combo box values return strings for numeric so if it is numeric then it needs
to be converted to numeric. I have used an If/then/else to do this.
Private Sub CommandButton1_Click()
Dim Response
Dim rngColDU As Range
Dim varDeleteVal As Variant
Dim i As Long
Response = MsgBox("Confirm to delete all rows with " _
& ComboBox4.Value & " in column DU", vbYesNo)
If Response = vbYes Then
'If ComboBox selection is numberic then
'Convert ComboBox selection from string to numeric
If WorksheetFunction.IsNumber(ComboBox4.Value) Then
varDeleteVal = Val(ComboBox4.Value)
Else
'Not numeric so just save to variable
varDeleteVal = ComboBox4.Value
End If
'Range is set from row 2 to end
'(Assumes row 1 is column headers)
With ActiveSheet
Set rngColDU = .Range(.Cells(2, "DU"), _
.Cells(.Rows.Count, "DU").End(xlUp))
End With
With rngColDU
'Must work backwards from bottom
'when deleting rows.
For i = .Rows.Count To 1 Step -1
If .Cells(i, 1) = varDeleteVal Then
.Cells(i, 1).EntireRow.Delete
End If
Next i
End With
Else
Exit Sub
End If
End Sub
