Bei vie­len Gele­gen­heit­en stellt sich die Forderung, dass aus einem gefilterten Bere­ich nur die sicht­baren Zellen kopiert und an ander­er Stelle wieder einge­fügt wer­den sollen. Per Hand ist das ja noch ganz nachvol­lziehbar, in VBA ist es aber auch nicht wirk­lich viel Aufwand. Hin­weis: Für das Grup­pieren und kopieren per Hand haben wir in diesem Beitrag eine alpha­betis­che Liste der Mit­glieder des 18. Deutschen Bun­destages, Stand 04.05.2015 erstellt. Diese Namen sollen nach Parteizuge­hörigkeit in einzelne Arbeits­blät­ter kopiert wer­den. Das Prinzip beruht auch auf gefilterten, also sicht­baren Zellen.

Die Auf­gabe hier ist eine ganz ähn­liche: Auss­chließlich die sicht­baren Werte sollen nach Spalte D kopiert wer­den. In der Muster-Mappe, Spalte A ste­hen ab Zeile 2 (Zeile 1 ist die Über­schrift) die Namen der Abge­ord­neten. In Spalte B wurde zum Zweck des Fil­terns, des Grup­pierens die Parteizuge­hörigkeit per Funk­tion einge­tra­gen. Diese UDF ist in  der Datei bere­its inte­gri­ert. Sie find­en den Code für die Funk­tion (natür­lich) in einem all­ge­meinen Mod­ul. Der Aufruf ist wie bei jed­er Funk­tion; in B2 geben Sie die Funk­tion ein: =Parteizugehoerigkeit(A2) und per Dop­pelk­lick auf das Aus­fül­lkästchen wer­den alle Parteien oder Fehler­w­erte einge­tra­gen.

Es gibt ver­schiedene Sichtweisen, unter­schiedliche Möglichkeit­en das zu bewälti­gen. Nicht alle Vorge­hensweisen sind opti­mal, wie das Ergeb­nis dann zeigen wird. Weit­er­hin ist unter Umstän­den ein ander­er Weg einzuschla­gen, wenn nach dem Fil­tern der let­zte sicht­bare Daten­satz nicht der let­zte Daten­satz der Gesamt-Liste ist. – Bei dieser Betra­ch­tung wird davon aus­ge­gan­gen, dass unter­halb der Abge­ord­neten-Liste keine weit­eren Dat­en ste­hen.

▲ 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 Dat­en auf gle­ich­er Höhe einge­fügt wer­den, wo die gefilterten Dat­en begin­nen. Das hat (schein­bar) den Vorteil, dass sie in Spalte B schön oben ste­hen. Aber es kann dur­chaus sein, dass durch die aus­ge­blende­ten Zeilen nur ein Teil der gefilterten und kopierten Werte sicht­bar ist. Wird der Fil­ter in Spalte A ent­fer­nt, sind auch die kom­plet­ten Dat­en in Spalte D sicht­bar. Das klingt vielle­icht ver­wirrend, aber schauen Sie ein­fach sel­ber… 

Aus­gangspunkt ist, dass ein Fil­ter geset­zt ist. Für die Abbil­dun­gen ver­suche ich so neu­tral wie möglich zu sein. Ich nutze als Fil­ter die Partei, nein die Frak­tion mit den meis­ten Abge­ord­neten, das ist zu dem Zeit­punkt die CDU/CSU.

Hier der Code, den Sie in das Mod­ul des Blattes schreiben kön­nen:

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 Ver­gle­ich: Die Aus­gangssi­t­u­a­tion vor dem fil­tern sieht so aus:

Die unge­filterten Dat­en

Nach dem Aufruf des Makros scheint alles OK zu sein. Ist es auch, wenn auf der anderen Seite doch nicht wirk­lich… Aber hier erst ein­mal das Ergeb­nis:

Die gefilterten Dat­en sind einge­tra­gen

Ist Ihnen eine gewisse Diskrepanz aufge­fall­en? Der in alpha­betis­ch­er Rei­hen­folge erste Abge­ord­nete der CDU/CSU heißt Stephan Albani, wie deut­lich in Spalte A zu sehen ist. Und in Spalte D? Da ste­ht Frau Alb­steiger ganz oben. Des Rät­sels Lösung erken­nen Sie, wenn der Fil­ter ent­fer­nt wird:

Der Fil­ter ist wieder aufge­hoben

… Und nun ist auch Herr Albani und auch Herr Auer­ham­mer in Spalte D sicht­bar. Faz­it: Nach dem Ein­fü­gen der Dat­en in der Zielspalte sollte der Fil­ter wieder deak­tiviert wer­den. Das geht übri­gend recht gut, wenn Sie ganz zum Schluss, direkt über dem End Sub noch diese Zeile in den Code ein­fü­gen:

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

Die If-Abfrage ist erforder­lich, da es bei nicht geset­ztem Aut­oFil­ter zu ein­er Fehler­mel­dung kommt.

▲ nach oben …

2. Daten unterhalb der letzten ungefilterten Zeile kopieren

Um auch in solchen Fällen alle kopierten Dat­en zu sehen, wenn der Fil­ter noch aktiv ist, kopieren Sie die Dat­en in die erste Zeile unter­halb der let­zten (unge­filterten) Zeile der Dat­en 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 bei­den anderen Möglichkeit­en (unter­halb der gefilterten Dat­en in Spalte A ste­hen weit­ere Dat­en) fol­gen in Kürze. Den Part mit der Markierung allerd­ings schiebe ich etwas her­aus. Das geht nun wirk­lich gaaanz ein­fach per Hand: Markieren, Strg und zu Ziel ziehen. Fer­tig.

▲ 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 Dat­en auf gle­ich­er Höhe einge­fügt wer­den, wo die unge­filterten Dat­en begin­nen. Das hat den Vorteil, dass sie in Spalte B schön oben ste­hen. Aber es kann dur­chaus sein, dass durch die aus­ge­blende­ten Zeilen nur ein Teil der gefilterten und kopierten Werte sicht­bar ist. Wird der Fil­ter in Spalte A ent­fer­nt, sind auch die kom­plet­ten Dat­en in Spalte D sicht­bar. Das haben Sie bere­its erkan­nt.

Hier gibt es nun eine weit­ere Beson­der­heit bzw. Erschw­er­nis: In Spalte A, wo die zu kopieren­den Dat­en ste­hen, muss die let­zte Daten­zeile des rel­e­van­ten Bere­ichs anders als gewohnt gesucht bzw. gefun­den wer­den. Nor­maler­weise wird (wie auch im Beispiel oben) mit ein­er Stan­dard­formel die let­zte belegte Zeile der Spalte bes­timmt. Da hier aber der auszuw­er­tende, also der zu fil­ternde Bere­ich wegen der darunter befind­lichen weit­eren Dat­en klein­er ist als die nor­mal berech­nete let­zte Zeile, kann das nicht greifen.

Prinzip­iell gibt es hier zwei Lösungsan­sätze: Sie markieren den entsprechen­den Daten­bere­ich per Hand oder aber Sie lassen das Excel machen. Im zweit­en Fall muss min­destens 1 leere Zelle unter­halb des zu fil­tern­den Daten­bere­ichs sein. Außer­dem darf bei den auszuw­er­tenden Dat­en (hier in Spalte A) keine Leerzeile sein. – Die zweite Zeile im Code ist ein Kom­men­tar, der kurz darauf hin­weist, wie der Ein­satzz­weck ist.

Da diese Vari­ante recht sel­ten genutzt wird und das Prinzip dem oben beschriebe­nen gle­ich ist, fol­gen hier der Codes für die ver­schiede­nen Möglichkeit­en ohne viele weit­ere Anmerkun­gen:

▲ 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 klas­sis­che Meth­ode, die in jedem Fall hin­haut.  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

Weit­ere Möglichkeit­en wer­den Sie sich anhand der Beispiele gewiss selb­st erar­beit­en kön­nen. Den zusam­menge­fassten Code kön­nen Sie hier als Text-Datei (*.zip-gepackt) herun­ter­laden und nach Bedarf in Ihr Pro­jekt ein­binden.

▲ nach oben …

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 3,00 € freuen … (← Klick mich!)