Excel Macro retaining X variable?

Question

I'm trying to create a graph from a spreadsheet, using a macro. I've got a graph that auto-generates, and displays the correct values, but seems to auto size to an old data set.

I've included a variable X, after it did not automatically resize, which is on a loop to round up to the nearest 50 after reaching the number of rows. When I first generated a graph from a data set with 2312 rows, X set to 2350, but now that I'm using a data set with 609 rows, X is still set to 2350.

''Graph Creator
'Data Selection
     Set sht = Sheets("Data")
     Sheets("Data").Select
 Dim LastColumn As Long
 Dim StartCell As Range
 Set StartCell = Range("D1")

 'Find Last Row and Column
   LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
   LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column
   sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
   ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
   ActiveChart.SetSourceData Source:=Range(StartCell, sht.Cells(LastRow, LastColumn))

'Graph axis sizing
     Dim X As Integer
     X = 100
     Do
     If X < LastRow Then
     X = X + 50
     Else
     End If
Loop Until X > LastRow


'Graph Positioning
    Sheets("Data").Select
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Graph"
    With ActiveChart.Parent
   .Height = 500 ' 'resized to approx 2/3 24" screen at 100% Zoom
   .Width = 1000 '
End With
    ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MaximumScale = X
With ActiveChart.Parent
.Left = Range("A1").Left
.Top = Range("A1").Top
End With
    ActiveChart.ChartTitle.Select
    Selection.Delete
    Application.ScreenUpdating = True
End Sub

When I first generated a graph from a data set with 2312 rows, X set to 2350, which is the intended output, but now that I'm using a data set with 609 rows, X is still set to 2350, instead of 650.

SJR · Accepted Answer · 2019-07-30T16:20:12.630

1

If your data are in column D try finding the last row this way

LastRow = range("D" & rows.count).end(xlup).row

SpecialCells(xlCellTypeLastCell) is unreliable as it can "remember" old data.

(Btw not sure what you're doing with X.)

This is worth a read too.

edited Jul 30 '19 at 16:20

answered Jul 30 '19 at 16:14

SJR

22,986
6
18
26

Not sure, can't see where you define the series? As per the link I would remove the Selects/Activates and use variables to refer to your ranges and charts and then you can refer to them directly. – SJR Jul 30 '19 at 16:29

Excel Macro retaining X variable?

1 Answers1