☰

如何根據該行中多個非連續列中的所有儲存格均為空白的標準隱藏 Excel 行

tag-icon • tag-icon microsoft-excel vba
如何根據該行中多個非連續列中的所有儲存格均為空白的標準隱藏 Excel 行

在 Excel 中，當且僅當行中多個非連續列中的所有儲存格均為空白時，如何隱藏行？我的工作表大約有 300 列，因此每次我想要執行此操作時，無法單獨單擊每一列。

我已經嘗試過下面的 VBA 程式碼，但它不允許超過兩個範圍。謝謝。

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160913
Dim xRg As Range
Application.ScreenUpdating = False
    For Each xRg In Range("B1:B825","D1:D825","F1:F825")
        If xRg.Value = "" Then
            xRg.EntireRow.Hidden = True

        Else
            xRg.EntireRow.Hidden = False
        End If
    Next xRg
Application.ScreenUpdating = True
End Sub

答案1

嘗試這個

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Long

    Application.ScreenUpdating = False
    With UsedRange
        For c = 2 To .Columns.Count Step 2
            .AutoFilter Field:=c, Criteria1:="<>"
        Next
    End With
    Application.ScreenUpdating = True
End Sub

答案2

有很多列需​​要檢查，以下通用解決方案將簡化程式碼輸入：

Private Sub Worksheet_Change(ByVal Target As Range)

  Const strcRowExtent As String = "1:825"
  Const strcColExtent As String = "B:BDB"

  Dim boolHideRow As Boolean
  Dim lngFirstColNumber As Long
  Dim rngRow As Range
  Dim rngVisibleRowExtent As Range
  Dim rngColumn As Range
  Dim rngColExtent As Range

  Set rngVisibleRowExtent = Range(strcRowExtent).SpecialCells(xlCellTypeVisible)
  Set rngColExtent = Range(strcColExtent)
  lngFirstColNumber = rngColExtent.Column
  Application.ScreenUpdating = False
    For Each rngRow In rngVisibleRowExtent.Rows
      boolHideRow = True
      For Each rngColumn In rngColExtent.Columns
        If (rngColumn.Column - lngFirstColNumber) Mod 2 = 1 Then
          'Skip every second column
        ElseIf rngColumn.Cells(rngRow.Row).Value2 <> "" Then
          boolHideRow = False
          Exit For
        End If
      Next rngColumn
      If boolHideRow Then Rows(rngRow.Row).EntireRow.Hidden = boolHideRow
    Next rngRow
  Application.ScreenUpdating = True

End Sub

解釋：

最初，從完整的行集中提取可見行集。這可以大大提高速度。

然後程式碼循環遍歷這組可見行。對於每一行，它會循環遍歷對應的列，檢查非空值並不是一旦找到第一個行就隱藏該行。 （隱藏行是僅當所有對應列均為空白時才會發生的預設操作。）

編輯#2：

第二個版本（v2.1）也隱藏了列，根據下面的OP評論：

Private Sub Worksheet_Change(ByVal Target As Range)
  ' v2.1
  Const lngcSkipRows As Long = 4
  Const strcRowExtent As String = "1:825"
  Const strcColExtent As String = "B:BDB"

  Dim boolHideRow As Boolean
  Dim lngFirstColNumber As Long
  Dim rngRow As Range
  Dim rngVisibleRowExtent As Range
  Dim rngColumn As Range
  Dim rngColExtent As Range
  Dim rngCol As Range
  Dim rngVisibleColExtent As Range
  Dim rngCroppedCol As Range

  Application.ScreenUpdating = False
    ' Hide rows
    Set rngVisibleRowExtent _
      = Range(strcRowExtent).Columns(1).SpecialCells(xlCellTypeVisible).EntireRow
    Set rngColExtent = Range(strcColExtent)
    lngFirstColNumber = rngColExtent.Column
    For Each rngRow In rngVisibleRowExtent.Rows
      boolHideRow = True
      For Each rngColumn In rngColExtent.Columns
        If (rngColumn.Column - lngFirstColNumber) Mod 2 = 1 Then
          'Skip every second column
        ElseIf rngColumn.Cells(rngRow.Row).Value2 <> "" Then
          boolHideRow = False
          Exit For
        End If
      Next rngColumn
      If boolHideRow Then Rows(rngRow.Row).EntireRow.Hidden = boolHideRow
    Next rngRow
    'Hide Columns
    Set rngVisibleColExtent _
      = Range(strcColExtent).Rows(1).SpecialCells(xlCellTypeVisible).EntireColumn
    For Each rngCol In rngVisibleColExtent.Columns
      Set rngCroppedCol _
        = rngCol _
          .Resize(Range(strcRowExtent).Rows.Count - lngcSkipRows) _
          .Offset(lngcSkipRows)
      If WorksheetFunction.CountA(rngCroppedCol) = 0 Then rngCol.Hidden = True
    Next rngCol
  Application.ScreenUpdating = True

End Sub

解釋：

事實證明，當存在隱藏行時提取可見列集（反之亦然）需要對提取公式進行輕微修改。

循環遍歷一組可見列的程式碼比遍歷行的程式碼更簡單，因為不需要內部循環。CountA()而是使用工作表函數。

請注意，似乎仍然有未隱藏的列，它們都是空白的。這些值位於隱藏行中。嚴格按照您的評論，不隱藏這些列是有意的。

注意：如果您對我的變數命名約定感到好奇，它是基於RVBA。

*代價是失去在編輯工作表時撤銷自動隱藏行的能力。如果需要，可以對此進行補救。

相關內容