Vba Code to Delete data based on a drop box choice

Question

I have a sheet that has a List box when that is selected codes appear , if a code is selected excel copies the data from a worksheet (with the same code ) into a quotation sheet.

If I make a change a select another code in the same list box I need excel to go and find the old data and delete it in the Quotation sheet.

Public Sub delete_selected_rows() Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range Dim lastRow As Long

With Worksheets("Q")
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    Set rng1 = .Range("B1:B" & lastRow)
End With

Set rng2 = Worksheets("SO").Range("D35")
For Each c In rng1
    If Not IsError(Application.Match(c.Value, rng2, 0)) Then
        'if value from rng1 is found in rng2 then remember this cell for deleting
        If rngToDel Is Nothing Then
            Set rngToDel = c
        Else
            Set rngToDel = Union(rngToDel, c)
        End If
    End If
Next c

If Not rngToDel Is Nothing Then rngToDel.CurrentRegion.Delete

End Sub

How can I get CurrentRegion to count an extra 30 rows the delete

Where is your problem? The way you describe your code everything works fine :-) — Variatus
Hi my problem is that I need to delete the data pasted in the Quotation sheet if the list box option is changed ,this I cant get right . — Penny

Variatus Variatus · Accepted Answer · 2017-04-09T14:34:49

VBA's ISERROR won't catch the error caused by a failed MATCH worksheet function. You need to construct that part differently.

Public Sub delete_selected_rows()

    Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
    Dim lastRow As Long
    Dim R As Long

    With Worksheets("Q")
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set rng1 = .Range("B1:B" & lastRow)
    End With

    Set rng2 = Worksheets("SO").Range("D35")
    For Each c In rng1
        On Error Resume Next
        R = 0
        R = WorksheetFunction.Match(c.Value, rng2, 0)
        On Error GoTo 0
        If R Then
            'if value from rng1 is found in rng2 then remember this cell for deleting
            ' R is the row number in rng2 where a match was found
            ' since rng2 is a single cell, R would always be 1, if found
            ' If rng2 = D35 MATCH be an overkill. Why not simply compare?
        Else
            If rngToDel Is Nothing Then
                Set rngToDel = c
            Else
                Set rngToDel = Union(rngToDel, c)
            End If
        End If
    Next c

    If Not rngToDel Is Nothing Then rngToDel.CurrentRegion.Delete
End Sub

Please observe my comments about rng2. Could there be some mistake? What does SO.D35 contain? If it contains a string of values one of which might be the one you look for MATCH is the wrong function to use.

It seems that you intend to put all items to be deleted on a spike and delete them in one go at the end. I'm not sure that is possible, and it's getting late for me. The more common approach would be to delete one row at a time, as you find them, because once you delete a row all row numbers below that row will change. You can run the entire code with ScreenUpdating turned off and set Application.ScreenUpdating = True after all the deleting has been done.

Vba Code to Delete data based on a drop box choice

1 Answers