VBA Update source data for chart

Question

I am using VBA to generate a chart.

With the following code I can select the data from column number 11 to the last column, which I have stored in LastColumnNumber variable:

ActiveChart.SetSourceData Source:=Union(Sheets(SheetName).Range(Cells(1, 11), Cells(1, LastColumnNumber)), Sheets(SheetName).Range(Cells(4, 11), Cells(4, LastColumnNumber)))

Because I have a lot of zero values before and after the data that I actually need displayed in the chart I am using the following methods to find out the addresses for the first and the last non-zero cells: https://stackoverflow.com/a/42413582/2012740

How can I update the fist piece of code to implement the solutions from the linked answer, and narrow the range for the chart accordingly?

Angelin Calu · Accepted Answer · 2017-02-23 14:11:51Z

2

I have mananged with the following approach:

Dim StartPoint1, StartPoint2, EndPoint1, EndPoint2 As String

StartPoint1 = Application.Evaluate("=ADDRESS(1,MATCH(TRUE,4:4<>0,0))")
EndPoint1 = Application.Evaluate("=ADDRESS(1,MAX((4:4>0)*COLUMN(4:4)))")

StartPoint2 = Application.Evaluate("=ADDRESS(4,MATCH(TRUE,4:4<>0,0))")
EndPoint2 = Application.Evaluate("=ADDRESS(4,MAX((4:4>0)*COLUMN(4:4)))")

And The chart source has become:

ActiveChart.SetSourceData Source:=Union(Sheets(SheetName).Range(StartPoint1 & ":" & EndPoint1), Sheets(SheetName).Range(StartPoint2 & ":" & EndPoint2))

Hope it helps someone! Cheers!

answered Feb 23, 2017 at 14:11

Angelin Calu

1,9158 gold badges24 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Update source data for chart

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related