Nur sichtbare Zellen kopieren

Nur sichtbare Zellen kopieren

Bei vielen Gelegenheiten stellt sich die Forderung, dass aus einem gefilterten Bereich nur die sichtbaren Zellen kopiert und an anderer Stelle wieder eingefügt werden sollen. Per Hand ist das ja noch ganz nachvollziehbar, in VBA ist es aber auch nicht wirklich viel Aufwand. Hinweis: Für das Gruppieren und kopieren per Hand haben wir in diesem Beitrag eine alphabetische Liste der Mitglieder des 18. Deutschen Bundestages, Stand 04.05.2015 erstellt. Diese Namen sollen nach Parteizugehörigkeit in einzelne Arbeitsblätter kopiert werden. Das Prinzip beruht auch auf gefilterten, also sichtbaren Zellen.

Die Aufgabe hier ist eine ganz ähnliche: Ausschließlich die sichtbaren Werte sollen nach Spalte D kopiert werden. In der Muster-Mappe, Spalte A stehen ab Zeile 2 (Zeile 1 ist die Überschrift) die Namen der Abgeordneten. In Spalte B wurde zum Zweck des Filterns, des Gruppierens die Parteizugehörigkeit per Funktion eingetragen. Diese UDF ist in  der Datei bereits integriert. Sie finden den Code für die Funktion (natürlich) in einem allgemeinen Modul. Der Aufruf ist wie bei jeder Funktion; in B2 geben Sie die Funktion ein: =Parteizugehoerigkeit(A2) und per Doppelklick auf das Ausfüllkästchen werden alle Parteien oder Fehlerwerte eingetragen.

Es gibt verschiedene Sichtweisen, unterschiedliche Möglichkeiten das zu bewältigen. Nicht alle Vorgehensweisen sind optimal, wie das Ergebnis dann zeigen wird. Weiterhin ist unter Umständen ein anderer Weg einzuschlagen, wenn nach dem Filtern der letzte sichtbare Datensatz nicht der letzte Datensatz der Gesamt-Liste ist. – Bei dieser Betrachtung wird davon ausgegangen, dass unterhalb der Abgeordneten-Liste keine weiteren Daten stehen.

A) Unterhalb der gefilterten Daten stehen keine weiteren (nicht sichtbare) Namen

1. Die Daten sollen nach D2 kopiert werden.
2. Die Daten sollen unterhalb der letzten Zeile des ungefilterten Bereichs kopiert werden.

B) Unterhalb der gefilterten Daten stehen weiteren Namen.

1. Die Daten sollen nach D2 kopiert werden.
2. Die Daten sollen unterhalb der letzten Zeile des ungefilterten Bereichs kopiert werden.

▲ nach oben …

A) Unterhalb der gefilterten Daten keine weiteren Werte

1. Die Daten sollen nach D2 kopiert werden.

Im Prinzip geht es darum dass die kopierten Daten auf gleicher Höhe eingefügt werden, wo die gefilterten Daten beginnen. Das hat (scheinbar) den Vorteil, dass sie in Spalte B schön oben stehen. Aber es kann durchaus sein, dass durch die ausgeblendeten Zeilen nur ein Teil der gefilterten und kopierten Werte sichtbar ist. Wird der Filter in Spalte A entfernt, sind auch die kompletten Daten in Spalte D sichtbar. Das klingt vielleicht verwirrend, aber schauen Sie einfach selber… 

Ausgangspunkt ist, dass ein Filter gesetzt ist. Für die Abbildungen versuche ich so neutral wie möglich zu sein. Ich nutze als Filter die Partei, nein die Fraktion mit den meisten Abgeordneten, das ist zu dem Zeitpunkt die CDU/CSU.

Hier der Code, den Sie in das Modul des Blattes schreiben können:

Sub CopyVisibleCells_1() 'by GMG-CC.de
   Dim lRow As Long
   
   lRow = Cells(Rows.Count, 1).End(xlUp).Row
   Range("A2:A" & lRow).SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Range("D2")
End Sub

Für Sie zum Vergleich: Die Ausgangssituation vor dem filtern sieht so aus:

Die ungefilterten Daten

Die ungefilterten Daten

Nach dem Aufruf des Makros scheint alles OK zu sein. Ist es auch, wenn auf der anderen Seite doch nicht wirklich… Aber hier erst einmal das Ergebnis:

Die gefilterten Daten sind eingetragen

Die gefilterten Daten sind eingetragen

Ist Ihnen eine gewisse Diskrepanz aufgefallen? Der in alphabetischer Reihenfolge erste Abgeordnete der CDU/CSU heißt Stephan Albani, wie deutlich in Spalte A zu sehen ist. Und in Spalte D? Da steht Frau Albsteiger ganz oben. Des Rätsels Lösung erkennen Sie, wenn der Filter entfernt wird:

Der Filter ist wieder aufgehoben

Der Filter ist wieder aufgehoben

… Und nun ist auch Herr Albani und auch Herr Auerhammer in Spalte D sichtbar. Fazit: Nach dem Einfügen der Daten in der Zielspalte sollte der Filter wieder deaktiviert werden. Das geht übrigend recht gut, wenn Sie ganz zum Schluss, direkt über dem End Sub noch diese Zeile in den Code einfügen:

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

Die If-Abfrage ist erforderlich, da es bei nicht gesetztem AutoFilter zu einer Fehlermeldung kommt.

▲ nach oben …

2. Daten unterhalb der letzten ungefilterten Zeile kopieren

Um auch in solchen Fällen alle kopierten Daten zu sehen, wenn der Filter noch aktiv ist, kopieren Sie die Daten in die erste Zeile unterhalb der letzten (ungefilterten) Zeile der Daten in Spalte A. Hier der Code:

Sub CopyVisibleCells_2() 'by GMG-CC.de
   Dim lRow As Long
   
   lRow = Cells(Rows.Count, 1).End(xlUp).Row
   Range("A2:A" & lRow).SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Range("B" & lRow + 2) '1 Leerzeile
   If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub

Anmerkung: Die beiden anderen Möglichkeiten (unterhalb der gefilterten Daten in Spalte A stehen weitere Daten) folgen in Kürze. Den Part mit der Markierung allerdings schiebe ich etwas heraus. Das geht nun wirklich gaaanz einfach per Hand: Markieren, Strg und zu Ziel ziehen. Fertig.

▲ nach oben …

B. Unterhalb der gefilterten Daten stehen weiteren Werte

1. Die Daten sollen nach D2 kopiert werden.

Im Prinzip geht es darum dass die kopierten Daten auf gleicher Höhe eingefügt werden, wo die ungefilterten Daten beginnen. Das hat den Vorteil, dass sie in Spalte B schön oben stehen. Aber es kann durchaus sein, dass durch die ausgeblendeten Zeilen nur ein Teil der gefilterten und kopierten Werte sichtbar ist. Wird der Filter in Spalte A entfernt, sind auch die kompletten Daten in Spalte D sichtbar. Das haben Sie bereits erkannt.

Hier gibt es nun eine weitere Besonderheit bzw. Erschwernis: In Spalte A, wo die zu kopierenden Daten stehen, muss die letzte Datenzeile des relevanten Bereichs anders als gewohnt gesucht bzw. gefunden werden. Normalerweise wird (wie auch im Beispiel oben) mit einer Standardformel die letzte belegte Zeile der Spalte bestimmt. Da hier aber der auszuwertende, also der zu filternde Bereich wegen der darunter befindlichen weiteren Daten kleiner ist als die normal berechnete letzte Zeile, kann das nicht greifen.

Prinzipiell gibt es hier zwei Lösungsansätze: Sie markieren den entsprechenden Datenbereich per Hand oder aber Sie lassen das Excel machen. Im zweiten Fall muss mindestens 1 leere Zelle unterhalb des zu filternden Datenbereichs sein. Außerdem darf bei den auszuwertenden Daten (hier in Spalte A) keine Leerzeile sein. – Die zweite Zeile im Code ist ein Kommentar, der kurz darauf hinweist, wie der Einsatzzweck ist.

Da diese Variante recht selten genutzt wird und das Prinzip dem oben beschriebenen gleich ist, folgen hier der Codes für die verschiedenen Möglichkeiten ohne viele weitere Anmerkungen:

▲ nach oben …

Sub CopyVisibleCells_3() 'by GMG-CC.de
   'Gefilterte Daten nicht markiert, Ziel = D2
   Dim lRow As Long
   Dim Ze As Long  'Zeile

   lRow = Range("A1").End(xlDown).Row
   For Ze = 2 To lRow
      If Rows(Ze).Hidden = False Then Exit For
   Next Ze
   Range(Cells(Ze, 1), Cells(lRow, 1)).Copy _
    Destination:=Range("D2")
   If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub

Das ist die klassische Methode, die in jedem Fall hinhaut.  Mit etwas weniger Code geht es aber auch fast immer:

Sub CopyVisibleCells_4() 'by GMG-CC.de
 'Gefilterte Daten nicht markiert, Ziel = D2
   Dim lRow As Long
   Dim Ze As Long 'Zeile

   lRow = Range("A1").End(xlDown).Row
   Range(Cells(2, 1), Cells(lRow, 1)).Copy _
    Destination:=Range("D2")
   If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub

▲ nach oben …

Sub CopyVisibleCells_5() 'by GMG-CC.de
   'Gefilterte Daten jetzt markiert, Ziel = D2
   'keine weiteren Daten unterhalb
   Dim lRow As Long, fRow As Long
   Dim Ze As Long 'Zeile
   Dim fRowNew As Long
   
   lRow = Range("A1").End(xlDown).Row
   fRow = Selection.Cells(1, 1).Row
   
   Selection.Copy Destination:=Range("A" & lRow + 2)
   If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
   Cells(lRow + 2, 1).Activate   'Der Übersicht wegen
End Sub

Weitere Möglichkeiten werden Sie sich anhand der Beispiele gewiss selbst erarbeiten können. Den zusammengefassten Code können Sie hier als Text-Datei (*.zip-gepackt) herunterladen und nach Bedarf in Ihr Projekt einbinden.

▲ nach oben …

Dieser Beitrag wurde unter Mit VBA/Makro, Ohne Makro/VBA, Tabelle und Zelle, Tipps und Tricks abgelegt und mit , verschlagwortet. Setze ein Lesezeichen auf den Permalink.