VBA Excel Draw line between cells based on cell value

0 votes
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 by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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
        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
answered Feb 6, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel Conditional Formatting based on Adjacent Cell Value

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

answered Oct 10, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Excel Define a range based on a cell value

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

answered Nov 8, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

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

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Excel VBA: Trying to read all files in folder based on cell input and output to another cell

Your array has no capacity for data ...READ MORE

answered Jan 24, 2023 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

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

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

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

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

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

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

VBA Change Cell colors based on value, and it can deal with single cell and multiple cells changes

Before looping through all of the cells ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,420 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP