By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Basic Excel Macro to create graphs

P: 78
I have a simple Excel Spreadsheet that continuously updates with data (both columns and rows). I am trying to build a macro that will start with the first row (which is actually row 2) and graph all the data in that row until an empty cell is found and stop. Then I need it to loop to the next row and graph that row until an empty cell found. I need it to continue row by row until and empty cell is found in Column A. I'd like each of the graphs to be on a seperate tab.

I've tried to use suggestions online and plug and play with different code and have only created a mess. Any help on how to set this up would be greatly appreciated!

Thanks.
Dec 10 '13 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,602
The following Code will dynamically create a 3D Clustered Column Chart on Sheet1 based on Data contained in Cells starting from Row2/Column A. The Data will consist of all Columns in Row 2 whose Cells are not Empty. No Validation is performed on the Values within these Cells.
Expand|Select|Wrap|Line Numbers
  1. Dim intCol As Integer
  2. Dim intRow As Integer
  3. Dim rng As Excel.Range
  4.  
  5. intRow = 2
  6. intCol = 1
  7.  
  8. Do Until Cells(intRow, intCol).Value = ""
  9.   intCol = intCol + 1
  10. Loop
  11.  
  12. Set rng = Range("A" & CStr(intRow) & ":" & Chr$(intCol + 63) & CStr(intRow))
  13.     rng.Select
  14.  
  15. Charts.Add
  16.  
  17. ActiveChart.ChartType = xl3DColumnClustered
  18. ActiveChart.SetSourceData Source:=rng, PlotBy:=xlRows
  19. ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
  20.  
  21. With ActiveChart
  22.   .HasTitle = False
  23.   .Axes(xlCategory).HasTitle = False
  24.   .Axes(xlSeries).HasTitle = False
  25.   .Axes(xlValue).HasTitle = False
  26. End With
This should be a good starting point for you.
Dec 10 '13 #2

P: 78
Thank you for the great start! This worked exactly like you said it would, however after the chart was created, a run-time error came up stating "The specified dimension is not valid for the current chart type". When I click "Debug", it highlights:

Expand|Select|Wrap|Line Numbers
  1. ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
Dec 11 '13 #3

P: 78
@ADezii

I apologize. I didn't know if I should've replied here or post new, so I did both.

Thank you for the great start! This worked exactly like you said it would, however after the chart was created, a run-time error came up stating "The specified dimension is not valid for the current chart type". When I click "Debug", it highlights:

Expand|Select|Wrap|Line Numbers
  1. ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
Thanks!
Dec 11 '13 #4

ADezii
Expert 5K+
P: 8,602
Try changing the offending Line of Code to:
Expand|Select|Wrap|Line Numbers
  1. ActiveChart.Location Where:=xlLocationAsNewSheet
Dec 11 '13 #5

P: 78
@ADezii

Perfect! Thanks for your help! After messing around for an hour or two, I figured out the rest of what I needed for this as well! Thanks for getting me started and following up!
Dec 11 '13 #6

ADezii
Expert 5K+
P: 8,602
Your are quite welcome. I guess you are no longer a WannebePrgmr but a FullFledgedPrgmr (LOL).
Dec 11 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.