So verbergen Sie Excel-Zeilen basierend auf dem Kriterium, dass alle Zellen in mehreren, nicht aufeinanderfolgenden Spalten in dieser Zeile leer sind

Question

Wie kann ich in Excel Zeilen genau dann ausblenden, wenn alle Zellen in mehreren, nicht aufeinanderfolgenden Spalten der Zeile leer sind? Mein Arbeitsblatt hat etwa 300 Spalten, daher kann ich nicht jedes Mal, wenn ich dies tun möchte, jede einzelne Spalte einzeln anklicken.

Ich habe den untenstehenden VBA-Code bereits ausprobiert, aber er lässt nicht mehr als zwei Bereiche zu. Danke.

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

Versuche dies

---

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

Da viele Spalten überprüft werden müssen, vereinfacht die folgende allgemeine Lösung die Codeeingabe:

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

Erläuterung:

Zunächst wird aus dem gesamten Zeilensatz der Satz der sichtbaren Zeilen extrahiert. Dies ermöglicht eine enorme Geschwindigkeitssteigerung.*

Der Code durchläuft dann diese sichtbaren Zeilen. Für jede Zeile durchläuft er die entsprechenden Spalten und sucht nach nicht leeren Werten.nichtAusblenden der Zeile, sobald die erste gefunden wird. (Das Ausblenden einer Zeile ist die Standardaktion, die nur dann erfolgt, wenn alle entsprechenden Spalten leer sind.)

---

BEARBEITEN #2:

Zweite Version (v2.1), die auch Spalten ausblendet, gemäß OP-Kommentar unten:

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

Erläuterung:

Es stellt sich heraus, dass das Extrahieren der Menge der sichtbaren Spalten, wenn ausgeblendete Zeilen vorhanden sind (und umgekehrt), eine geringfügige Änderung der Extraktionsformel erfordert.

Der Code, der die sichtbaren Spalten durchläuft, ist einfacher als der für die Zeilen, da keine innere Schleife erforderlich ist. CountA()Stattdessen wird die Arbeitsblattfunktion verwendet.

Beachten Sie, dass es immer noch nicht ausgeblendete Spalten geben könnte, die alle leer sind. Diese haben Werte in ausgeblendeten Zeilen. Das Nichtausblenden dieser Spalten ist Absicht, streng nach Ihrem Kommentar.

---

Hinweis: Wenn Sie neugierig auf meine Variablenbenennungskonvention sind, basiert sie aufRVBA.

* _{Auf Kosten des Verlusts der Möglichkeit, automatisch ausgeblendete Zeilen rückgängig zu machen, während das Blatt bearbeitet wird. Dies kann bei Bedarf behoben werden.}