Is it possible to chart hidden figures (data)

[Aussiebear]

Just a quick question. Is it possible to chart hidden data?

[jdelano]

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]

Okay.... I'm going to have to experiment with this a bit

[jdelano]

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

[Dave]

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]

Just a quick question. Is it possible to chart hidden data?

Yes. Col B is hidden


31560

31561

31562

[Aussiebear]

Thank you to everyone who has helped here.