Creating a larger table from input data

Hello experts, I am using excel (version 2202) on a PC with windows 10. I have a situation where I need to input x and y coordinates in a table and automatically receive a larger list of coordinates...

excel

Excel for web

Formulas and Functions

HansVogelaar

Mar 28, 2022

CHende77

Here is a macro that will create the output range:

Sub CreateOutput()
    Dim s As Long
    Dim t As Long
    Dim m As Long
    Application.ScreenUpdating = False
    Range("D3:E" & Rows.Count).ClearContents
    m = Range("A" & Rows.Count).End(xlUp).Row
    s = 3
    t = 3
    For s = 3 To m
        If s > 3 And Range("B" & s).Value <> Range("B" & s - 1).Value Then
            Range("D" & t).Value = Range("A" & s).Value
            Range("E" & t).Value = Range("B" & s - 1).Value
            t = t + 1
        End If
        Range("D" & t).Value = Range("A" & s).Value
        Range("E" & t).Value = Range("B" & s).Value
        t = t + 1
    Next s
    Application.ScreenUpdating = True
End Sub

Sub CreateOutput() Dim s As Long Dim t As Long Dim m As Long Application.ScreenUpdating = False Range("D3:E" & Rows.Count).ClearContents m = Range("A" & Rows.Count).End(xlUp).Row s = 3 t = 3 For s = 3 To m If s > 3 And Range("B" & s).Value <> Range("B" & s - 1).Value Then Range("D" & t).Value = Range("A" & s).Value Range("E" & t).Value = Range("B" & s - 1).Value t = t + 1 End If Range("D" & t).Value = Range("A" & s).Value Range("E" & t).Value = Range("B" & s).Value t = t + 1 Next s With ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Chart .SetSourceData Source:=Range("D2:E" & t - 1) .HasTitle = False End With Application.ScreenUpdating = True End Sub

Forum Discussion

Creating a larger table from input data

Resources