Collect or not to collect

[showard1]

Hi allI have a little challenge.I have a VBA routing that inserts a series of objects (simple shapes) in specific places on a worksheet according to a number of user inputs. in each instance I know the name of each object, and it is suffixed with a sequential number which I also have control of ieshaperange.name = "bar" & i where i is a sequential integer, and does not finish at a set max valueSimple so far.The problem I am having is to work out how to delete all the shapes at the start of a new session. I cant "select all" as there are some objects (shapes) that I dont want deleted.The first idea I had was to place them in a collection, which works as far as adding to the collection, but I cant then work out a way to then select the objects in the collection to delete them. Secondly I dont know if the objects stay in the collection once the macro routine has stopped running.Alternatively I could consider adding them to a group as I go, but this begs, a can you sequentially add to a group, and secondly can I force the group name as I can an object name.Alternatively I am missing an easier route. Many thanks in advance for anyone who can guide me.Si

 

[Andrew Poulsom]

Welcome to the Board.

If you know part of the shapes' names you can loop around them all and test the name with the Instr function or the Like operator before deleting.

 

[Yard]

Maybe

Sub DeleteShapes()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
    If Left(shp.Name, 3) = "bar" Then
        If IsNumeric(Trim(Right(shp.Name, Len(shp.Name) - Len("bar")))) Then
            shp.Delete
        End If
    End If
Next shp
End Sub

 

[showard1]

Thanks that routine worked fine, I am thinking still about using the routine to group or "collect" the objects together into one element, so that the initialisation routine (deleting) is quicker. any ideasSimon

 

[Yard]

Thanks that routine worked fine, I am thinking still about using the routine to group or "collect" the objects together into one element, so that the initialisation routine (deleting) is quicker. any ideasSimon

Silly question, but if it works then what's driving the need to do something else? Is this method too slow?

Seems like overkill to create a collection of objects, when the single fate of that collection is to be immediately deleted!

 

[showard1]

YardOK I need to explain a little further.The application is a timed process, that has a ticker for the user. Once they start, they have predeterminded time to complete the task, and its that task that puts up the different shape elements.My thinking was that if I grouped the shapes at the end of the process, when I came up with a new instance of the routine, there would be a single "delete" instance, as the clock started running, rather than the looping multiple "check and delete".The other option I guess is a separate "clear" then "start" processThanks for your input so far.Si
PS why are my carriage returns not showing in the posting ?

 

[Yard]

Still don't understand, but try this to create a ShapeRange object containing the relevant shapes:

Sub DeleteShapesArray()
Dim oShp As Shape, oShpR As ShapeRange
Dim wks As Worksheet
Dim vShapes()
Dim i As Integer
Const sID As String = "bar"
Set wks = ActiveSheet
For Each oShp In wks.Shapes
    If Left(oShp.Name, Len(sID)) = sID Then
        If IsNumeric(Trim(Right(oShp.Name, Len(oShp.Name) - Len(sID)))) Then
            i = i + 1
            ReDim Preserve vShapes(1 To i)
            vShapes(i) = oShp.Name
        End If
    End If
Next oShp
If Not IsArrayEmpty(vShapes) Then
    Set oShpR = wks.Shapes.Range(vShapes)
End If
If Not oShpR Is Nothing Then
    oShpR.Delete
End If
End Sub
 
Function IsArrayEmpty(a As Variant) As Boolean
'function from jindon
    IsArrayEmpty = Len(Join(a, "")) = 0
End Function