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

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

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


Capture.JPG

Capture2.JPG

Capture3.JPG

[Aussiebear]

Thank you to everyone who has helped here.