I have a sheet with a lot of data, my user will choose a value from list in
combo box4, I need a macro to delete every row where the combo box value does
not occur in column DU

Thanks

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

--
Regards,

OssieMac


"Miree" wrote:

I have a sheet with a lot of data, my user will choose a value from list in
combo box4, I need a macro to delete every row where the combo box value does
not occur in column DU

Thanks

Hi Ossie, I believe the OP wanted to delete if the data in DU did NOT match
the ComboBox value. Probably should be as below.

If .Cells(i, 1) < varDeleteVal Then
.Cells(i, 1).EntireRow.Delete


"OssieMac" wrote:

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

--
Regards,

OssieMac


"Miree" wrote:

I have a sheet with a lot of data, my user will choose a value from list in
combo box4, I need a macro to delete every row where the combo box value does
not occur in column DU

Thanks

Hi JLG,

Thanks for picking up on that. I am sure the OP will appreciate it.

--
Regards,

OssieMac


"JLGWhiz" wrote:

Hi Ossie, I believe the OP wanted to delete if the data in DU did NOT match
the ComboBox value. Probably should be as below.

If .Cells(i, 1) < varDeleteVal Then
.Cells(i, 1).EntireRow.Delete


"OssieMac" wrote:

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

--
Regards,

OssieMac


"Miree" wrote:

I have a sheet with a lot of data, my user will choose a value from list in
combo box4, I need a macro to delete every row where the combo box value does
not occur in column DU

Thanks