Just a quick question. Is it possible to chart hidden data?
Just a quick question. Is it possible to chart hidden data?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
Okay.... I'm going to have to experiment with this a bit
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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
Last edited by jdelano; 04-28-2024 at 08:15 AM.
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
HTH. DaveSub 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
Yes. Col B is hidden
Capture.JPG
Capture2.JPG
Capture3.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Thank you to everyone who has helped here.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link