View Full Version : [SOLVED:] Is it possible to chart hidden figures (data)
Aussiebear
04-28-2024, 06:18 AM
Just a quick question. Is it possible to chart hidden data?
jdelano
04-28-2024, 07:06 AM
So, once you hide the columns the chart empties. You can though, access the values in the hidden columns using VBA. So, you can fill a chart via VBA with the hidden columns data.
Aussiebear
04-28-2024, 07:33 AM
Okay.... I'm going to have to experiment with this a bit
jdelano
04-28-2024, 07:50 AM
Put the hidden range data in an array, then use that to fill the chart.
excel - Create Chart from Array data and not range - Stack Overflow (https://stackoverflow.com/questions/10570023/create-chart-from-array-data-and-not-range)
edit: super simple of course
Dim hiddenCol1 As Range
Dim hiddenCol2 As Range
Dim rangeChart As Chart
Dim chartSeries As Series
Dim chartValues() As Integer
Dim chartXValues() As String
Set hiddenCol1 = ActiveSheet.Range("B1:B5")
Set hiddenCol2 = ActiveSheet.Range("C1:C5")
Set rangeChart = ActiveSheet.ChartObjects("Chart 4").Chart
Set chartSeries = rangeChart.SeriesCollection.NewSeries
rangeChart.ChartType = xlLine
ReDim chartValues(hiddenCol1.Count - 1)
ReDim chartXValues(hiddenCol1.Count - 1)
For rw = 2 To hiddenCol1.Count
chartXValues(rw - 1) = hiddenCol1.Cells(rw, 1)
chartValues(rw - 1) = hiddenCol2.Cells(rw, 1)
Next rw
chartSeries.Values = chartValues
chartSeries.XValues = chartXValues
Hi Aussiebear. This link has the manual way to show hidden cells and some interesting VBA that you might want to trial....
How to show hidden data in Excel chart - Excel Off The Grid (https://exceloffthegrid.com/using-hidden-cells-in-a-chart/)
Sub ToggleChartDisplayHiddenRows()
'Declare and assign variable
Dim cht As Chart
Set cht = ActiveChart
'Ignore errors if no chart active
On Error Resume Next
'Toggle hidden data visibility
cht.PlotVisibleOnly = Not cht.PlotVisibleOnly
On Error GoTo 0
End Sub
HTH. Dave
Paul_Hossler
04-29-2024, 06:33 AM
Just a quick question. Is it possible to chart hidden data?
Yes. Col B is hidden
31560
31561
31562
Aussiebear
04-29-2024, 02:29 PM
Thank you to everyone who has helped here.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.