bar chart one row with varying colums excel 2003

[cometorta]

Hi,

Can someone help me with the following.

I need a macro to create a bar chart:

the data to be charted is in one row (let's row 2); however the number of columns varies between 1 and 10. I want to a flexible macro that counts the number of columns with data, and then used that number to do the bar chart. The label for the data points are in row 1.


Thanks in advance,

Francisco

 

[sijpie]

cometora,
remember that excel has an excellent macro recording tool that will give you most of the information you need.

I recorded creating a graph and need to change only one thing. The code you need (assuming that C2 is in your range to be charted:

Sub CreateGraph()
'
'
    Range("C2").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C2").CurrentRegion
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

where the recorded macro read
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C2:E2")
I changed this to the line above, with .currentregion
range("c2").currentregion will expand the address to all the cells with values attached to C2. So that will inlcude all your columns with a value

 

[cometorta]

sijpie,

Awesome! thank you very much.

One more questions can i change

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C2").CurrentRegion

to

ActiveChart.SetSourceData Source:=ActiveSheet.Range("C2").CurrentRegion

I want to replicate the same macro across different sheets and workbooks. So i need a macro that is so restrictive

Thanks

Francisco

 

[cometorta]

I think i need

CurrentSheet.

instead of

ActiveSheet

Correct?

Francisco

 

[cometorta]

Sijpie,

I have run your code. However the CurrentRegion command graphs everything around the range "C2". I have data in the next 2 rows which I don't want to graphs. Is there such a thing as CurrentRow ?

Thanks,

Francisco

 

[sijpie]

no there isn't such a thing as currentrow. What you then need to do is set up the range properly.
so you want to count the number of columns first and then use this to define the range. Then you set the chart:

Sub CreateGraph()
'
Dim CRows As Long
Dim chRange As String
Dim ActSheet As String
    CRows = Range("C2").CurrentRegion.Columns.Count
    chRange = "C2:C" & CRows
    ActSheet = ActiveSheet.Name
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets(ActSheet).Range(chRange)
    ActiveChart.Location Where:=xlLocationAsObject, Name:=ActSheet
End Sub

You need to store the name of the sheet in the variable (ActSheet I called it) as while the chart is being created, Excel will first create a chart sheet, so the active sheet has suddenly changed during the macro!