Как скрыть строки Excel на основе критерия: все ячейки в нескольких непоследовательных столбцах этой строки являются пустыми

Question

В 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

Answer 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

---

Answer 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.

* _{Ценой потери возможности отмены строк автоматически скрываются при редактировании листа.Это может быть исправлено при необходимости.}