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?
Feb 6, 2023 in Others 886 views

## 1 answer to this question.

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
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```
• 63,420 points

## Excel Conditional Formatting based on Adjacent Cell Value

The row number used in the formula ...READ MORE

## Excel Define a range based on a cell value

Let's say that cells A1, A2, A3, ...READ MORE

## Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE