Showing charts with hidden data

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

I have chart vba code that will create a chart in a specific location,
but when the data is hidden with a hide row macro the chart goes
blank. Now I tried the chart options unchecking the checkbox "plot
visible cells only." I also recorded a macro and unchecked the box in
options and took that code and transplanted that into the chart vba
code. But it still doesn't work. Any suggestions. Here's my code.

Sub US_Flour_Volumes_Q1()

Range("D6").Select
ActiveCell.FormulaR1C1 = "Q1"

For Each chrt In Worksheets("Position Sheet").ChartObjects
chrt.Delete
Next chrt

Application.ScreenUpdating = False
Set FlourChart = Charts.Add

FlourChart.Location _
Where:=xlLocationAsObject, _
Name:="Position Sheet"
Set FlourChart = ActiveChart
With FlourChart
.SetSourceData _
Source:=Sheets("Data").Range("E422:G422,E433:G433"), _
PlotBy:=xlRows
.HasTitle = True
.ChartType = xlColumnClustered
.HasLegend = False
.SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
.SeriesCollection(1).Name = "=""2006"""
.SeriesCollection(2).Name = "=""2007"""
.ChartTitle.Characters.Text = "US White Flour Volumes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

FlourChart.HasLegend = True
FlourChart.Legend.Position = xlBottom
With FlourChart.ChartTitle
.Left = 105
.Top = 6
End With
FlourChart.ChartArea.Select

Application.ScreenUpdating = False
Set FlourChart = Charts.Add
FlourChart.Location _
Where:=xlLocationAsObject, _
Name:="Position Sheet"
Set FlourChart = ActiveChart
With FlourChart
.SetSourceData _
Source:=Sheets("Data"). _
Range("E423:G423,E434:G434"), _
PlotBy:=xlRows
.HasTitle = True
.ChartType = xlColumnClustered
.HasLegend = False
.SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
.SeriesCollection(1).Name = "=""2006"""
.SeriesCollection(2).Name = "=""2007"""
.ChartTitle.Characters.Text = "US Wheat Flour Volumes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

FlourChart.HasLegend = True
FlourChart.Legend.Select
Selection.Position = xlBottom
Range("A1").Select

Dim iChart As Long
Dim nCharts As Long
Dim dTop As Double
Dim dLeft As Double
Dim dHeight As Double
Dim dWidth As Double
Dim nColumns As Long

dTop = 500 ' top of first row of charts
dLeft = 90 ' left of first column of charts
dHeight = 200 ' height of all charts
dWidth = 250 ' width of all charts
nColumns = 3 ' number of columns of charts
nCharts = ActiveSheet.ChartObjects.Count

For iChart = 1 To nCharts
With ActiveSheet.ChartObjects(iChart)
.Height = dHeight
.Width = dWidth
.Top = dTop + Int((iChart - 1) / nColumns) * dHeight
.Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth
End With
Next

End Sub


Ryan
 
Somewhere you have to say

ActiveChart.PlotVisibleOnly = False

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______
Click to expand...

I put that in here but still doesn't work. Did I put it in the correct
place?

Application.ScreenUpdating = False
Set FlourChart = Charts.Add
FlourChart.Location _
Where:=xlLocationAsObject, _
Name:="Position Sheet"
Set FlourChart = ActiveChart
With FlourChart
.SetSourceData _
Source:=Sheets("Data"). _
Range("E423:G423,E434:G434"), _
PlotBy:=xlRows
.HasTitle = True
.ChartType = xlColumnClustered
.HasLegend = False
.SeriesCollection(1).XValues = "=Data!R321C5:R321C16"
.SeriesCollection(1).Name = "=""2006"""
.SeriesCollection(2).Name = "=""2007"""
.ChartTitle.Characters.Text = "US Wheat Flour Volumes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
ActiveChart.PlotVisibleOnly = False
<---------------------------------------------------------
End With
 
If it's inside the With/EndWith, you don't need the ActiveChart, just

.PlotVisibleOnly = False

I don't know why it didn't work, unless another chart was active.

- Jon
 
If it's inside the With/EndWith, you don't need the ActiveChart, just

.PlotVisibleOnly = False

I don't know why it didn't work, unless another chart was active.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______
Click to expand...

I figured it out. I put jon's code of ActiveChart.PlotVisibleOnly =
False
in between these codes in the code above.

PlotBy:=xlRows
' put jon's code here
.HasTitle = True

then it works great if anyone wants in the future.
 
Back
Top