VBA code to set up bar graph. 1

[gjsala]

I have two columns H and I which have information I would like to put into a bar graph using VBA code. I have tried to use the record button but when I insert that code into the existing code my program breaks. The number of items in the cells will vary between different sheets. Any ideas where I could look to figure this problem out? Thanks in advance!

 

[SkipVought]

Hi,

Where is your code that breaks?

Charts & VBA faq707-4811

Skip,

_[red][/red]

 

[gjsala]

SkipVought,
Here is the code I have so far:
Sub Test1()
Dim myRange As Range
Dim numRows As Integer
Set myRange = ActiveSheet.Range("H1:I6500")
numRows = Application.CountA(myRange)
Charts.Add
ActiveChart.ChartType = xlBarClustered
ActiveChart.SetSourceData Source:=Sheets("Array_1").Range(numRows), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Array_1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Test"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Components"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stop"
End With
End Sub

I'm trying to get a range in columns H & I only. I'm not sure if I Dim the right range variable. The range in column H will not be the same range but H and I will have the same number for example H1 and I1 will have some item in it. Thanks in advance.

 

[SkipVought]

I see problems in these two areas

Sub Test1()
Dim myRange As Range
Dim numRows As Integer[b]
Set myRange = ActiveSheet.Range("H1:I6500")
numRows = Application.CountA(myRange)[/b]
    Charts.Add
    ActiveChart.ChartType = xlBarClustered
    ActiveChart.SetSourceData Source:=[b]Sheets("Array_1").Range(numRows)[/b], PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Array_1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Test"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Components"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stop"
    End With
End Sub

First you refer to the active sheet with range H1:I6500.

Then you assign numRows as the count of the cells in the range (which is 13,000 if every cell has a value) when it should be

numRows = myRange.Rows.Count

Then you say that the range is on sheet Array_1. IS that the active sheet?

And you say that the RANGE on that sheet is the numRows.

Maybe what you want is for the source data range to be referenced on sheet Array_1 in columns H & I with a varible number of rows?

Sub Test1()
    Dim myRange As Range
    Dim numRows As Integer
'using CurrentRegion assumes that _
  1 the table is contiguous and _
  2 the table is isolated from all other data
    Set myRange = Sheets("Array_1").Range("H1").CurrentRegion
'    numRows = myRange.Rows   'you don't need this
    Charts.Add
    ActiveChart.ChartType = xlBarClustered
'the source is myRange
    ActiveChart.SetSourceData Source:=myRange, PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Array_1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Test"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Components"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stop"
    End With
End Sub

Skip,

_[red][/red]

 

[gjsala]

Skip,
Thanks for the response. I ran the new code and the information from columns A-G were added to the graph. I'm looking to only add information from columns H & I to the graph. Do you have any ideas?

Thanks!

 

[SkipVought]

'using CurrentRegion assumes that _
1 the table is contiguous and _
2 the table is isolated from all other data

You apparently do NOT have you table ISOLATED from other data.

So describe ALL the data on the sheet to me.

Skip,

_[red][/red]

 

[gjsala]

Skip,
Columns A through G have information in each cell. This information I don't want on the graph nor do I want this counted in the number of rows to gather information for the graph. I would like to code to only look at columns H and I to fill in information for the graph. For example if column H has information in Cells H1 through H10 and cells I1 through I10 have information, these cells are the only ones I want captured in the code and displayed on the graph from the sheet. Thanks for looking into this!

 

[SkipVought]

    Set myRange = Sheets("Array_1").Range("H1:I6500")

Use AutoFilter to display only rows with data in column H.

Skip,

_[red][/red]

 

[PHV]

You may also try this:
Set myRange = Intersect(Sheets("Array_1").Range("H1").CurrentRegion, Sheets("Array_1").Range("H:I"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

What's really good about PHV's solution is that, unless you are working with a static range of data, using absolute row values can lead to problems if your data exceeds that hard-coded row value.

Have a star!

Skip,

_[red][/red]

 

[gjsala]

SkipVought and PHV,
Thanks for your information! I'm still having problems only selecting information from columns H&I . The code:

Set myRange = Intersect(Sheets("Array_1").Range("H1").CurrentRegion, Sheets("Array_1").Range("H:I"))

looks at the information from columns A through G and counts those rows to determine how many items to put in the graph. I only want columns H & I to determine this. Code:

Set myRange = Sheets("Array_1").Range("H1:I6500")

puts all 6500 rows into the graph not what is in columns H and I. Do you have any other ideas? Thanks!

 

[SkipVought]

Set myRange = Intersect(Sheets("Array_1").Range("H1").CurrentRegion, Sheets("Array_1").Range("H:I"))

takes the Intersection of the current region and columns H:I. You must have extraneous data in rows that is affecting your chart. If you have formulas that return "" or 0, they are included.

Skip,

_[red][/red]

 

[SkipVought]

...try using the AutoFilter on the desired columns to limit the display in the Chart.

Skip,

_[red][/red]

 

[SkipVought]

...or

Set myRange = Sheets("Array_1").Range([H1], [I65536].end(xlup))

Skip,

_[red][/red]