☰

Como ocultar linhas do Excel com base no critério de que todas as células em múltiplas colunas não consecutivas nessa linha estão em branco

tag-icon • tag-icon microsoft-excel vba
Como ocultar linhas do Excel com base no critério de que todas as células em múltiplas colunas não consecutivas nessa linha estão em branco

No Excel, como posso ocultar 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 sempre que quero fazer isso.

Já tentei o código VBA abaixo, mas 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

Responder1

Experimente 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

Responder2

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 na velocidade.*

O código então percorre esse conjunto de linhas visíveis. Para cada linha, ele percorre as colunas apropriadas, verificando valores que não estão em branco enãoocultando 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.)

EDITAR #2:

Segunda versão (v2.1) que também oculta colunas, conforme 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 ligeira modificação na fórmula de extração.

O código que percorre o conjunto de colunas visíveis é mais simples do que aquele para as linhas, pois não é necessário um loop interno. A função de planilha CountA()é usada em seu lugar.

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

Nota: Se você está curioso sobre minha convenção de nomenclatura de variáveis, ela é baseada emRVBA.

* Ao custo de perder a capacidade de desfazer linhas ocultadas automaticamente à medida que a planilha é editada. Isso pode ser remediado, se necessário.

informação relacionada