excel - 如何隐藏Excel中过滤列表中没有值的列？

Question

我有一个包含各种化学品及其规范的大型电子表格；然而，并非每种化学品都需要在每列中都有一个值，因此有很多空白单元格。我想知道当我从下拉列表过滤器中选择某列时，如果其中没有值，是否可以隐藏该列？

例如，我单击下拉列表并选择“氢氧化钾”，我希望它隐藏“水分”列，因为其中没有值。

现在的样子:

我之前尝试过使用一些 VBA 代码，但我似乎不知道如何将其合并到下拉列表过滤器中。

Answer 1

不幸的是，没有用于应用/更改过滤器来触发宏的事件。但是，您可以操纵 Event、Worksheet_Calculate 来实现所需的结果，因为修改过滤器会计算工作表。 IE。每次工作表计算时，都会触发宏!

<小时/>

所以现在我们需要将过滤器链接到计算。任何方程都可以做到这一点，所以我只是在我的示例中设置 K1 = L1 。理想情况下，这将位于看不见的地方(理想情况下位于上次使用的列标题旁边，以避免隐藏未使用的列)

<小时/>

该宏仅通过计算可见行的非空单元格实例来利用Aggregate函数。应用过滤器后，任何只有 1 个可见单元格的列都将被隐藏。我们使用 1 作为基线，因为由于标题的原因，所有列至少有 1 个可见单元格。

将以下代码粘贴到工作表 Specifications 上的 VBE 中。这在模块或工作簿模板中不起作用。

Option Explicit

Private Sub Worksheet_Calculate()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Specifications")
Dim LCol As Long: LCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

On Error GoTo SafeExit

Dim HeaderCell As Range

Application.ScreenUpdating = False
    Columns.EntireColumn.Hidden = False
    Rows.EntireRow.Hidden = False

    For Each HeaderCell In ws.Range(ws.Cells(1, 1), ws.Cells(1, LCol))
        If Application.WorksheetFunction.Aggregate(3, 7, HeaderCell.EntireColumn) = 1 Then
            HeaderCell.EntireColumn.Hidden = True
        End If
    Next HeaderCell

SafeExit:
Application.ScreenUpdating = True

End Sub

<小时/>

TLDR

确保工作表至少有 1 个方程。当您应用/修改过滤器时，您会强制进行计算。当您强制进行计算时，就会触发宏。当您触发宏时，您将隐藏只有 1 个可见单元格(这将是标题)的所有列。

如果运行缓慢，您可以将范围添加到列的Union 中，并在循环完成后隐藏Union(符合您条件的所有列)。