excel - 如何在 Excel 图表中显示隐藏行，但不显示隐藏列？

Question

有没有办法让 Excel 图表在隐藏行中绘制数据，但不在隐藏列中？我已经知道如何在右键单击图表时使用“选择数据”选项转到“隐藏和空单元格”选项，该选项提供“在隐藏的行和列中显示数据”的选项。但是，我找不到在隐藏行中显示数据而不在隐藏列中显示数据的方法，并且希望有人能够提出 VBA 解决方案。

非常感谢，

杰夫

Answer 1

我只是为你鼓掌。需要注意的是，使用图表可能非常棘手。此代码仅适用于某些类型的图表，您可能需要进行一些调整才能使用您的特定数据集。

Option Explicit

Sub RemoveHiddenColumns()

Dim myChart As ChartObject
Set myChart = ActiveSheet.ChartObjects("Chart 1") 'place in here whichever chart you need to reference, asssumes the chart is on the activesheet

myChart.Activate 'first activate the chart

Dim i As Integer

For i = 1 To ActiveChart.SeriesCollection.Count 'loop through each series

    Dim strText As String, strCol As String, strSht As String, intCol As Integer

    strText = Split(ActiveChart.SeriesCollection(i).Formula, ",")(2) 'extract sheet name and column of series
    strSht = Split(strText, "!")(0) 'get sheet name of series
    strCol = Split(strText, "!")(1) 'get column range of series

    Dim wks As Worksheet
    Set wks = Worksheets(strSht)

    If wks.Range(strCol).EntireColumn.Hidden = True Then 'if the column is hidden
        ActiveChart.SeriesCollection(i).Delete 'remove the series
    End If

Next


End Sub