Place vertical connector in the middle of cells in column

[perola.rike]

I wish to programatically to add a connector in the center of the cells in range B5:B11. I do have the following code below, but how to place the connector in the center (not left)?


Sub AddArrow()
Dim shp As Shape
Set shp = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, Range("B1").Left, Range("B5").Top, Range("B11").Left, Range("B11").Top)
shp.Select
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub

 

[mikerickson]

This will add an arrow between the centers of the first and last cell of a region.

Sub AddArrow2()
    Dim shp As Shape, TLCell As Range, BRCell As Range
    
    With Range("B5:B11")
        Set TLCell = .Cells(1, 1)
        Set BRCell = .Cells(.Rows.Count, .Columns.Count)
    End With
    Set shp = ActiveSheet.Shapes.AddConnector(msoConnectorStraight, _
                            TLCell.Left + TLCell.Width / 2, TLCell.Top + TLCell.Height / 2, _
                            BRCell.Left + BRCell.Width / 2, BRCell.Top + BRCell.Height / 2)
    shp.Line.EndArrowheadStyle = msoArrowheadOpen
End Sub

 

[perola.rike]

Perfect, thank you! I worked just as needed.