Link to home
Start Free TrialLog in
Avatar of John Carney
John CarneyFlag for United States of America

asked on

With Excel VBA: Reorganizing the Plot Order on a chart by legend name, not number

I need to re-position the series in a chart based on the values of the series. The values to be sorted have corresponding series/legend names in Column B.So instead of moving the series up and down like this:
ActiveChart.SeriesCollection(4).PlotOrder = 1

Open in new window

I need to be able to specify the plot order of each series based on its name like this:  
ActiveChart.SeriesCollection(Series Name).PlotOrder = 1

Open in new window

Dim top As Range, btm As Range, topRw As Long, btmRw As Long, SrzNm As String
Set top = [B1].End(xlDown)
Set btm = top.End(xlDown)
topRw = top.Row - (top.Row - 1)
btmRw = btm.Row - [B1].End(xlDown).Row + 1
For i = topRw To btmRw
SrzNm = Cells(top.Row + i - 1, 2)
'ActiveChart.SeriesCollection(the PlotOrder number of the series collection named "SrzNm").PlotOrder = i
Next ii

Open in new window

How would I write line 8 so that the PlotOrder of the series collection with the name of SrzNm would be the value of i?

Is this enough info?

Avatar of Rgonzo1971

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of John Carney


Thanks, Rgonzo. I tried that  - or what I thought was that! - before and it didn't work. Thank you for making me try it again.

~ John