☰

Как скрыть строки 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) также скрывает столбцы, как указано в комментарии автора ниже:

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()Вместо этого используется функция рабочего листа.

Обратите внимание, что все еще могут казаться не скрытыми столбцы, которые все пустые. У них есть значения в скрытых строках. Не скрывать эти столбцы намеренно, строго согласно вашему комментарию.

Примечание: Если вам интересно мое соглашение об именовании переменных, оно основано наРВБА.

* Ценой потери возможности отменять строки, автоматически скрытые при редактировании листа. Это можно исправить, если требуется.

Связанный контент