Search code examples
excelvbalinedrawingcell

How can I join cells with a vertical line?

How do I join two selected cells with a vertical line?

If the cells F6 and F11 were the ones to join,
cells to join

one might type

=LINEJOIN(F6,F11)

and get a centered vertical line running from the middle of the bottom edge of F6 down to the middle of the top edge of F11.
joined cells

This is for a family tree project.

Solution

  • Try this out

    Function LineJoin(rngStart As Range, rngEnd As Range)
    Dim shp As Shape, nm As String, ws As Worksheet
    Dim st, sl, et, el
    
    Set ws = Application.ThisCell.Worksheet ' `ThisCell` = the cell with the formula
    'a [unique] name for the line
    nm = rngStart.Address(False, False) & "_" & rngEnd.Address(False, False)
    
    On Error Resume Next
    ws.Shapes(nm).Delete  'remove any already-added line
    On Error GoTo 0
    
    'calculate start/end left & top
    sl = rngStart.Left + (rngStart.Width / 2)
    st = rngStart.Top + rngStart.Height
    el = rngEnd.Left + (rngEnd.Width / 2)
    et = rngEnd.Top
    
    Set shp = ws.Shapes.AddConnector(msoConnectorStraight, sl, st, el, et)
    shp.Name = nm  'name the line
    LineJoin = nm  'return the name of the added line
End Function