Deleting a line

  • Hi everyone

    VBA/Excel yet again boggles my brain and leaves me screaming at my monitor...

    I have written a macro that draws lines on a spreadsheet like so:

    ActiveSheet.Shapes.AddLine(20, 35, 20, 50).Select
    Selection.ShapeRange.Line.Weight = 2.25
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.Style = msoLineSingle

    Now I would also like the macro to be able to delete this line at any given time too. However, the only information the macro will have is the co-ordinates of the line (ie. for above, 20, 35, 20, 50). The macro will not know the 'line number'.

    I have tried out various ideas but cannot accomplish what I want. Does anyone know how to do this?

    Any tips would save me a lot of stress!
    Many thanks

  • Code
    1. Dim shpLine as Shape
    2. Set shpLine = ActiveSheet.Shapes.AddLine(20, 35, 20, 50)
    3. shpLine.Line.Weight = 2.25
    4. shpLine.Line.Visible = msoTrue
    5. shpLine.Line.Style = msoLineSingle

    And then later you can do:

    1. shpLine.Delete

    The other option is to name the Shape and then loop through the shapes and then check the names.


  • A line is defined by Top,Left,Width & Height and with Flips. You could try to test each shape to see if it matches your co-ordinates, but you will have problems as the drawn shape does not always have exactly the dimensions you have entered to draw it.

    Why do you need to delete by co-ords? How many lines are you drawing and what are you using them for?


  • Hi TJ

    My spreadsheet is basically a model for a giant network of computer systems. Between each node is a cable and I have written a routing algorithm that maps out a route between two nodes and visually displays the cable on the screen. I need to write a macro that deletes these cables and I figured that the best way to do this would be to use the same routing algortihm, but to delete the lines (cable) rather than display them.

    I have got literally hundreds of these lines (cables) which is why I didn't really want to have to name each one.

    Ideally, I need some code that will delete a line once it knows it's co-ordinates (since co-ordinates are used in the routing algorithm).

    I suspect this is not possible in VBA, but I thought I'd present my problem on this forum just in case.

    Thanks for you replies TJ. If you think of any other solutions I'd be most grateful.


  • Try naming the shape, then no matter how the shape changes you can still delete it by reference to the shape name. The On Error Resume Next is needed as the code fails in adding if it already exists or fails in delete if it does not exist. You could put messages in if the code fails.

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Do you want to delete the lines one at a time or all at once?

    If you want to delete them all at once but leave other shapes, you could use the Type property I think from memory it is msoLine.

    If you want to delete them one at a time why not use the co-ords as part of the name as suggested above. You could then make use to the OnAction property to delete the lines as you click them. I did that as part of a macro to mark locked cells, by putting the cell reference into the name I could use the OnAction property to set the shapes to unlock the cell and then delete themselves.


  • I would need to delete all of the lines in that particular route at the same time.
    TJ - what do you mean by this:

    "If you want to delete them all at once but leave other shapes, you could use the Type property I think from memory it is msoLine"

    Sounds useful but I don't know what you mean.

    Thanks for your help,

  • Mmm, I wouldn't really want to delete all shapes - I'll remember that code for future reference though. I think I'll simply name all the lines, as you suggested earlier, and store the all line names for each cable route I've laid down on a 'hidden' sheet.

    Really appreciate your help on this one TJ.

  • The following code posted earlier works great for drawing a line and assigning the line to shpLine.

    Dim shpLine As Shape
    Set shpLine = ActiveSheet.Shapes.AddLine(20, 35, 20, 50)
    shpLine.Line.Weight = 2.25
    shpLine.Line.Visible = msoTrue
    shpLine.Line.Style = msoLineSingle

    But what if shpLine needs to be a variable, so that each line drawn will have a different name?
    Kind thanks in advance to anyone with a solution

  • You most probably want a variation of the code that Bill gave in post#7