Leere Spalten aus manuell markiertem Bereich ausblenden

Question

Guten Tag, es sollen Leere Spalten aus manuell markiertem Bereich ausblendet werden, z.B. ich markiere den Bereich von C3:O33, sollte in Spalte D3:D33 kein wert enthalten sein (nur Formeln) so soll diese Spalte über mein erstelltes Formularsteuerelement ausgeblendet werden. Danke

Answer 1

Hi ihr Beiden,

Außerdem ist im Beispielbild nur Zeile 1 markiert. Selection.Columns gibt in dem Fall nur die einzelnen Zellen der ersten Zeile zurück.

Das ist nicht korrekt, denn

1. es wird mit meinem Code nur die Schleife von der ersten bis zur letzten Zelle der 1. Zeile der Selection ausgeführt und

2. wird mit diesem Teil Selection(intZaehler) die laufende Spalte innerhalb der markierten Spalten angesprochen.

Was die Verwendung von Application.Sum betrifft kann man zustimmen. Das lässt sich aber umgehen, wenn man stattdessen die Bedingung Application.CountIf verwendet.

Hier der Komplettcode für das Ausblenden von Spalten und Zeilen:

Sub SpaltenZeilenAusblenden()
    Dim intZaehler As Integer
    Dim lngZeile As Long
    For intZaehler = 1 To Selection.Rows(1).Cells.Count
        If Application.CountIf(Selection.Columns(intZaehler), "") = Selection.Rows.Count Then Selection.Columns(intZaehler).EntireColumn.Hidden = True
    Next intZaehler
    For lngZeile = Selection.Columns(1).Rows.Count To 1 Step -1
        If Application.CountIf(Selection.Rows(lngZeile), "") = Selection.Columns.Count Then Selection.Rows(lngZeile).EntireRow.Hidden = True
    Next lngZeile
End Sub

Bis später, Karin

Answer 2

Hallo Karin, wenn möglich sollte beim erneuten Betätigen vom Formularsteuerelement alles wieder eingeblendet werden. Trotzdem schon mal vielen Dank.

Answer 3

Eine fertige Lösung mit Apps Script ist bereits möglich – vorausgesetzt, Makros sind kein Problem. Die Aufgabe lässt sich gut per Skript lösen: Dabei wird jede Spalte im manuell gewählten Bereich geprüft, ob sichtbare Inhalte vorhanden sind. Falls eine Spalte komplett leer ist (auch wenn dort Formeln stehen, die nur "" zurückgeben), wird sie automatisch ausgeblendet.

Ich empfehle, statt getValues() lieber getDisplayValues() zu verwenden, da getValues() auch leere Strings ("") als Werte zurückliefert, was insbesondere bei Formeln wie =WENN(...; "") zu unerwartetem Verhalten führen kann. getDisplayValues() orientiert sich hingegen an dem, was tatsächlich in der Zelle angezeigt wird – also näher an dem, was man als "leer" wahrnimmt.

Answer 4

Hi Jelena,

man kann prüfen, ob das Blatt ausgeblendete Zellen enthält:

Sub SpaltenZeilenAusblenden()
    Dim intZaehler As Integer
    Dim lngZeile As Long
    ' stimmt Adresse der sichtbaren Zellen mit der Adresse aller Zellen überein - dann Ausblenden erforderlich
    If Cells.SpecialCells(xlCellTypeVisible).Address = Cells.Address Then
        For intZaehler = 1 To Selection.Rows(1).Cells.Count
            If Application.CountIf(Selection.Columns(intZaehler), "") = Selection.Rows.Count Then Selection.Columns(intZaehler).EntireColumn.Hidden = True
        Next intZaehler
        For lngZeile = Selection.Columns(1).Rows.Count To 1 Step -1
            If Application.CountIf(Selection.Rows(lngZeile), "") = Selection.Columns.Count Then Selection.Rows(lngZeile).EntireRow.Hidden = True
        Next lngZeile
    Else
        Cells.EntireColumn.Hidden = False
        Cells.EntireRow.Hidden = False
    End If
End Sub

Bedingung für das Funktionieren dieses Codes ist jedoch, dass es in keinem anderen Bereich des Tabellenblattes ausgeblendete Zellen gibt. Falls das doch der Fall sein sollten, dann musst du im Code einen Maximal-Zellbereich vorgeben, innerhalb dessen geprüft werden soll ob es ausgeblendete Zellen gibt.

Bis später, Karin

Answer 5

Hallo Karin, ja es in keinem anderen Bereich des Tabellenblattes ausgeblendete Zellen gibt z.B. von C3 bis AJ66. Bitte um vollständiges Makro. Besten Danke

Answer 6

Hallo Karin, bitte um Entschuldigung ich hatte bei der vorherigen Nachricht einen falschen Text eingegeben bitte um Nachsicht. Es kann sein, dass irgendwo ein Filter gesetzt wurde, dadurch gibt es in anderen Bereich des Tabellenblattes ausgeblendete Zellen z.B. von C3 bis AJ66. Bitte um vollständiges Makro. Besten Danke

Answer 7

Hi Jelena,

man kann Zeilen/Spalten nicht teilweise ausblenden - nur die gesamte Zeile bzw. die gesamte Spalte. Wenn sich also in denselben Zeilen, die du markieren und bei erfüllter Bedingung per Makro ausblenden willst, zusätzlich noch durch Autofilter gefilterte Zeilen befinden, dann ist dein Vorhaben nicht durchführbar.

Bis später, Karin

Answer 8

Hallo Karin, das Sub SpaltenZeilenAusblenden() Makro soll zuerst prüfen ob z.B. außer den markierten Bereich ein Filter gesetzt ist, dann den Filter zuerst aufheben. Danke

Answer 9

Hi Jelena,

die folgende Codezeile prüft, ob im Tabellenblatt irgendwo ein Autofilter gesetzt wurde und falls ja, wird die Filterung aufgehoben:

   If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData

Schreibe diese Codezeile an den Anfang meines Codes.

Bis später, Karin

Answer 10

Hallo Karin, die 1. Zeile soll immer ausgeblendet sein, sobald ich dieses Makro ausführe, wird die 1. Zeile eingeblendet der Rest passt. Danke