I am trying to create a macro that helps me delete all the rows in which the checkbox is TRUE. So far I created one that deletes all the rows (in which checkboxes are TRUE) however the checkboxes are still there. I attached two photos of how the table looks like and how I want it to be look like after running the macro.

Table from which I would like to delete row 2 and 4 with the checkbox

desired table after running the macro

my code

CodePudding user response：

Checkboxes (and other objects) are not a child of the cell they are hovering over, so you would have to remove them manually.

Without your source code it's a bit of a guess how you could implement this best, but here's a pseude code;

'Loop through checkboxes
for each obj in ActiveWorksheet.OLEObjects
   If obj.Object.Value = True Then
        'Your code to remove the rows
        ...

        'Remove the checkbox itself
        obj.Delete
    End If
next obj

Next you have to make sure that the subsequent checkboxes shift up when you delete rows. To achieve this make sure that you select "Move with cells" option under the position properties for each checkbox.

CodePudding user response：

Based on what I see (and default naming conventions), you're using standard forms checkboxes (i.e. not ActiveX).
Given that, the following code will do what you want.

Sub RemoveCheckedRows()
    
    Dim ctCB As CheckBox

''' Process all checkboxes in the ActiveSheet
    For Each ctCB In ActiveSheet.CheckBoxes
    
    ''' If the checkbox is checked, remove the row it's in and the checkbox itself
        If ctCB = 1 Then
            ctCB.TopLeftCell.EntireRow.Delete
            ctCB.Delete
        End If
        
    Next ctCB

End Sub

Notes:

1. You need to ensure the top left corner of each checkbox is actually within a cell in the row to which it refers
2. You don't need to link a cell to the checkbox value (though you still can if you want)