VBA to loop through rows based on a criteria and delete

[Will85]

Hello,

Every cell in column A will either be blank, or will have the text value "Yes"
I need a macro that can go through column A, and if any cell in column A = "Yes" delete all rows that meet that criteria. For exmaple if A2, A99, and A1002 = "yes", then delete rows 2, 99, and 1002.

Id like to learn how to best accomplish the above as a stand alone VBA, but to further complicate what I really need is the above +

In column B every cell will have a combobox, I need the deletion to also delete the combobox in the applicable rows.

Right now, if a user were to right click a row and delete, the combobox is not deleted.

Each combobox name may not be consecutive, so I need to somehow define that within the range of the entire row to be deleted, any shape in that row should be deleted first, and then delete the row.

When there is a combobox, there is always a linked cell in column B, if that helps find the comboboxes . . .

Thank you in advance for your help

 

[Puertorekinsam]

You Can try something like this:

When you loop through and delete the rows, it's going to break the linked cell value. So this version has the code delete the rows first, and then loop through the combo boxes and get rid of any that do not have linked cells to them.

Sub CycleRowsAndColumns()
    Dim Cel As Range
    Dim Shp As Shape
    Dim ro As Integer
    Set Cel = Cells(1, 1)
    
    Do Until Cel.End(xlDown).Row = 1048576
    Set Cel = Cel.End(xlDown)
        ro = Cel.Row
        If Cel = "Yes" Then
            Rows(ro).Delete
            Set Cel = Cells(ro, 1)
        Else
            Set Cel = Cel.End(xlDown)
        End If
    Next
    
    For Each Shp In ActiveSheet.Shapes
        If Shp.Type = 8 Then ' This is a Drop Down Combo box
            Shp.Select
            If Selection.LinkedCell = "#REF!" Then Shp.Delete
        End If
    Next


End Sub

Another option would be to loop through all the comboboxes before you delete the row and then delete the one that has a linked cell = "$B$" & ro.
However this could cause you to loop through a lot of the combo boxes every time you delete a row... The method I used only loops through them once.

 

[Will85]

You Can try something like this:

When you loop through and delete the rows, it's going to break the linked cell value. So this version has the code delete the rows first, and then loop through the combo boxes and get rid of any that do not have linked cells to them.

Sub CycleRowsAndColumns()
    Dim Cel As Range
    Dim Shp As Shape
    Dim ro As Integer
    Set Cel = Cells(1, 1)
    
    Do Until Cel.End(xlDown).Row = 1048576
    Set Cel = Cel.End(xlDown)
        ro = Cel.Row
        If Cel = "Yes" Then
            Rows(ro).Delete
            Set Cel = Cells(ro, 1)
        Else
            Set Cel = Cel.End(xlDown)
        End If
    Next
    
    For Each Shp In ActiveSheet.Shapes
        If Shp.Type = 8 Then ' This is a Drop Down Combo box
            Shp.Select
            If Selection.LinkedCell = "#REF!" Then Shp.Delete
        End If
    Next


End Sub

Another option would be to loop through all the comboboxes before you delete the row and then delete the one that has a linked cell = "$B$" & ro.
However this could cause you to loop through a lot of the combo boxes every time you delete a row... The method I used only loops through them once.

I get a compile error: Next without For

The first Next is highlighted

 

[Puertorekinsam]

Sorry for the delayed response, I was out of the country. try this:

Sub CycleRowsAndColumns()
    Dim Cel As Range
    Dim Shp As Shape
    Dim ro As Integer
    Set Cel = Cells(1, 1)
    
    Do Until Cel.End(xlDown).Row = 1048576
    Set Cel = Cel.End(xlDown)
        ro = Cel.Row
        If Cel = "Yes" Then
            Rows(ro).Delete
            Set Cel = Cells(ro, 1)
        Else
            Set Cel = Cel.End(xlDown)
        End If
    loop
    
    For Each Shp In ActiveSheet.Shapes
        If Shp.Type = 8 Then ' This is a Drop Down Combo box
            Shp.Select
            If Selection.LinkedCell = "#REF!" Then Shp.Delete
        End If
    Next


End Sub