how to filter the shape by text and delete selected shapes (In the example, I'm trying to filter shapes with text "No" and delete)

userEPIl1 0
Viewed 34
Sub delete_shapes()    
    Dim i As Integer

    ActiveSheet.Shapes.SelectAll

    For i = 1 To 4 Step 1
    If i < 5 Then

    For Each shape In ActiveSheet.Shapes
     'it has to match with shapes of text "NO"

      If Selection.ShapeRange(i).TextFrame2.TextRange.Characters.Text = "No" Then
   

     shape.Select
        Selection.Delete

     Else
     Debug.Print Selection.ShapeRange(i).TextFrame2.TextRange.Characters.Text
  ' running through the range of index
    i = i + 1
      End If

    Next shape

    End If

    Next i
End Sub

enter image description here

vba excel arrays
1 Answers

When deleting items from a collection it's often a good idea to go "backwards" so that deleted items don't affect the indexes of later items.

Sub delete_shapes()
    Dim i As Long, ws As Worksheet, txt, shp As Shape
    
    Set ws = ActiveSheet
    Application.ScreenUpdating = False
    For i = ws.Shapes.Count To 1 Step -1
        Set shp = ws.Shapes(i)
        txt = shp.TextFrame2.TextRange.Characters.Text
        Debug.Print shp.Name, txt
        If txt = "No" Then shp.Delete
    Next i
    Application.ScreenUpdating = True
End Sub
useryB020
Related