VBA Excel Draw line between cells based on cell value

Question

I'm straining to think of the work. I must draw a line depending on value from one cell to the next (if not 0). There could only be one value in each row; the others are all zeros. Line's beginning and finish were in the cell's middle.

Anyone have programming experience?

Answer 1

In accordance with your description and with the connectors' ends centred on the cells, this code generates msoConnectorStraight forms on the RangeToConnect parameter.
Note: One line controls whether empty rows should be skipped; comment it if you choose.

Sub ConnectValues(RangeToConnect As Range)
    Dim ws As Worksheet
    Set ws = RangeToConnect.Parent
    
    'Clear the existing shapes on the range
    Dim s As Shape
    For Each s In ws.Shapes
        If s.Type = msoConnectorStraight And _
            Not Application.Intersect(RangeToConnect, s.TopLeftCell) Is Nothing And _
            Not Application.Intersect(RangeToConnect, s.BottomRightCell) Is Nothing Then
            s.Delete
        End If
    Next s
    
    'Add the connectors to the range
    Dim cell1 As Range, cell2 As Range, r As Range, c As Range
    For Each r In RangeToConnect.Rows
        Set cell2 = cell1
        Set cell1 = Nothing 'Breaks the line on empty rows, to be commented if they should be ignored.
        For Each c In r.Cells
            If VBA.Len(c.Value) > 0 Then
                Set cell1 = c
                Exit For
            End If
        Next c
        If Not cell1 Is Nothing And Not cell2 Is Nothing Then
            ws.Shapes.AddConnector _
                msoConnectorStraight, _
                cell1.Left + cell1.Width / 2, cell1.Top + cell1.Height / 2, _
                cell2.Left + cell2.Width / 2, cell2.Top + cell2.Height / 2
        End If
    Next r
End Sub