VBA Chart Switch Row/Column Help

nocturnal028

New Member
Joined
Aug 13, 2008
Messages
9
Is there a way in VBA to do the equivalent of pushing the 'Switch Row/Column' button seen in the Excel 2007 chart wizard when selecting the data range for a chart?

Essentially my chart data range is correct but the x and y axis values are backwards. I am trying to avoid having to write statements to add each individual series collection with an associated .XValue to force the data to be on the right axis.

I'd like to be able to use my one "ActiveChart.SetSourceData Source:=" line of code to set the data range instead of doing multiple .SeriesCollection.NewSeries (then also setting the .Name, .Values and .XValues) lines for each series. Then ideally after the one "ActiveChart.SetSourceData Source:=" line of code I would have a line of code telling the axis values to switch and all of my pain would be gone!

Does a line of code to tell the values to switch exist?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

nocturnal028

New Member
Joined
Aug 13, 2008
Messages
9
I found the answer to my own question... If anyone else comes up against this, the VBA code to switch the data being dsiplayed back and forth between the x and y axis on a chart is:

ActiveChart.PlotBy = xlColumns
ActiveChart.PlotBy = xlRows
 

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
Thank you for posting your own answer. I think you just saved me a few hours of work. I try to do this when I come across my own answers as well.
 

paramesh.n

New Member
Joined
Nov 4, 2009
Messages
8
Awesome..worked fantastic...

Thanks Buddy...

Paramesh
 

all4excel

Active Member
Joined
Feb 15, 2008
Messages
435
I found the answer to my own question... If anyone else comes up against this, the VBA code to switch the data being dsiplayed back and forth between the x and y axis on a chart is:

ActiveChart.PlotBy = xlColumns
ActiveChart.PlotBy = xlRows

This indeed is a very useful TIP to SWAP from ROWS TO COLUMNS..

THanks a lot

Warm Regards
all4excel
 

pdevito3

Board Regular
Joined
Dec 17, 2013
Messages
246
Also agree. Good solution. Thanks.
 

nletizio

New Member
Joined
May 9, 2018
Messages
1
Just wanted to say that 10 years after OP this just saved me a ton of time and headache. Thanks for posting your own solution!
 

ericwhitetiger

New Member
Joined
Oct 11, 2018
Messages
1
I found the answer to my own question... If anyone else comes up against this, the VBA code to switch the data being dsiplayed back and forth between the x and y axis on a chart is:

ActiveChart.PlotBy = xlColumns
ActiveChart.PlotBy = xlRows
Tiptop. Saved much heartache. Excel macro recording didn't record this, so your insight was invaluable.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,756
Members
406,497
Latest member
Bryanlim

This Week's Hot Topics

Top