Como ocultar as linhas do Excel com base no critério de todas as células em várias colunas não consecutivas nessa linha estarem em branco

Question

No Excel, como posso ocultar as linhas se e somente se todas as células em várias colunas não consecutivas na linha estiverem em branco? Minha planilha tem cerca de 300 colunas, então não posso clicar individualmente em todas as outras vezes que quiser fazer isso.

Eu já tentei o código VBA abaixo, mas ele não permitirá mais de dois intervalos. Obrigado.

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

Tente isso

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

Tendo muitas colunas para verificar, a seguinte solução generalizada simplificará a entrada de código:

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

Explicação:

Inicialmente, o conjunto de linhas visíveis é extraído do conjunto completo de linhas. Isso permite uma grande melhoria de velocidade. *

O código então percorre esse conjunto de linhas visíveis. Para cada linha, ele percorre as colunas apropriadas, verificando valores não em branco e não ocultando a linha assim que a primeira for encontrada. (Ocultar uma linha é a ação padrão que ocorre somente se todas as colunas apropriadas estiverem em branco.)

EDIT # 2:

Segunda versão (v2.1), que também oculta colunas, conforme o comentário do OP abaixo:

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

Explicação:

Acontece que extrair o conjunto de colunas visíveis quando há linhas ocultas (e vice-versa) requer uma pequena modificação na fórmula de extração.

O código que percorre o conjunto de colunas visíveis é mais simples que o das linhas, já que não é necessário um loop interno. A função da planilha CountA() é usada no lugar.

Observe que ainda pode haver colunas ocultas que estão todas em branco. Estes têm valores em linhas ocultas. Não esconder essas colunas é intencional, estritamente de acordo com o seu comentário.

Note: If you are curious about my variable naming convention, it is based on RVBA.

* _{Ao custo de perder a capacidade de desfazer linhas auto-ocultas conforme a folha é editada. Isso pode ser remediado, se necessário.}