Nur sichtbare Zellen kopieren

Nur sichtbare Zellen kopieren

Bei vie­len Gele­gen­heit­en stellt sich die For­de­rung, dass aus ei­nem ge­fil­ter­ten Bere­ich nur die sicht­baren Zel­len ko­piert und an ander­er Stel­le wie­der einge­fügt wer­den sol­len. Per Hand ist das ja noch ganz nachvol­lziehbar, in VBA ist es aber auch nicht wirk­lich viel Auf­wand. Hin­weis: Für das Grup­pieren und ko­pie­ren per Hand ha­ben wir in die­sem Bei­trag eine alpha­betis­che Lis­te der Mit­glieder des 18. Deut­schen Bun­destages, Stand 04.05.2015 er­stellt. Die­se Na­men sol­len nach Parteizuge­hörigkeit in ein­zel­ne Arbeits­blät­ter ko­piert wer­den. Das Prin­zip be­ruht auch auf ge­fil­ter­ten, also sicht­baren Zel­len.

Die Auf­gabe hier ist eine ganz ähn­liche: Auss­chließlich die sicht­baren Wer­te sol­len nach Spal­te D ko­piert wer­den. In der Mus­ter-Map­pe, Spal­te A ste­hen ab Zei­le 2 (Zei­le 1 ist die Über­schrift) die Na­men der Abge­ord­neten. In Spal­te B wur­de zum Zweck des Fil­terns, des Grup­pierens die Parteizuge­hörigkeit per Funk­tion einge­tra­gen. Die­se UDF ist in  der Da­tei bere­its inte­gri­ert. Sie find­en den Code für die Funk­tion (natür­lich) in ei­nem all­ge­meinen Mod­ul. Der Auf­ruf ist wie bei jed­er Funk­tion; in B2 ge­ben Sie die Funk­tion ein: =Par­tei­zu­ge­hoe­rig­keit(A2) und per Dop­pelk­lick auf das Aus­fül­lkästchen wer­den alle Par­tei­en oder Fehler­w­erte einge­tra­gen.

Es gibt ver­schiedene Sicht­wei­sen, unter­schiedliche Möglichkeit­en das zu bewälti­gen. Nicht alle Vorge­hensweisen sind opti­mal, wie das Ergeb­nis dann zei­gen wird. Weit­er­hin ist un­ter 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 Ge­samt-Lis­te ist. – Bei die­ser Betra­ch­tung wird da­von aus­ge­gan­gen, dass unter­halb der Abge­ord­neten-Lis­te kei­ne weit­eren Dat­en ste­hen.

A) Unter­halb der ge­fil­ter­ten Dat­en ste­hen kei­ne weit­eren (nicht sicht­bare) Na­men

1. Die Dat­en sol­len nach D2 ko­piert wer­den.
2. Die Dat­en sol­len unter­halb der let­zten Zei­le des unge­filterten Bere­ichs ko­piert wer­den.

B) Unter­halb der ge­fil­ter­ten Dat­en ste­hen weit­eren Na­men.

1. Die Dat­en sol­len nach D2 ko­piert wer­den.
2. Die Dat­en sol­len unter­halb der let­zten Zei­le des unge­filterten Bere­ichs ko­piert wer­den.

▲ nach oben …

A) Un­ter­halb der ge­fil­ter­ten Da­ten kei­ne wei­te­ren Wer­te

1. Die Da­ten sol­len nach D2 ko­piert wer­den.

Im Prin­zip geht es dar­um dass die ko­pier­ten Dat­en auf gle­ich­er Höhe einge­fügt wer­den, wo die ge­fil­ter­ten Dat­en begin­nen. Das hat (schein­bar) den Vor­teil, dass sie in Spal­te B schön oben ste­hen. Aber es kann dur­chaus sein, dass durch die aus­ge­blende­ten Zei­len nur ein Teil der ge­fil­ter­ten und ko­pier­ten Wer­te sicht­bar ist. Wird der Fil­ter in Spal­te A ent­fer­nt, sind auch die kom­plet­ten Dat­en in Spal­te D sicht­bar. Das klingt vielle­icht ver­wirrend, aber schau­en 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ög­lich zu sein. Ich nut­ze als Fil­ter die Par­tei, 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 Blat­tes schrei­ben 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 ungefilterten Daten

Die unge­filterten Dat­en

Nach dem Auf­ruf des Ma­kros scheint al­les OK zu sein. Ist es auch, wenn auf der an­de­ren Sei­te doch nicht wirk­lich… Aber hier erst ein­mal das Ergeb­nis:

Die gefilterten Daten sind eingetragen

Die ge­fil­ter­ten Dat­en sind einge­tra­gen

Ist Ih­nen eine ge­wis­se Dis­kre­panz aufge­fall­en? Der in alpha­betis­ch­er Rei­hen­folge ers­te Abge­ord­nete der CDU/CSU hei­ßt Ste­phan Al­ba­ni, wie deut­lich in Spal­te A zu se­hen ist. Und in Spal­te 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 Filter ist wieder aufgehoben

Der Fil­ter ist wie­der aufge­hoben

… Und nun ist auch Herr Al­ba­ni und auch Herr Auer­ham­mer in Spal­te D sicht­bar. Faz­it: Nach dem Ein­fü­gen der Dat­en in der Ziel­spal­te soll­te der Fil­ter wie­der deak­tiviert wer­den. Das geht übri­gend recht gut, wenn Sie ganz zum Schluss, di­rekt über dem End Sub noch die­se Zei­le in den Code ein­fü­gen:

If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

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

▲ nach oben …

2. Da­ten un­ter­halb der letz­ten un­ge­fil­ter­ten Zei­le ko­pie­ren

Um auch in sol­chen Fäl­len alle ko­pier­ten Dat­en zu se­hen, wenn der Fil­ter noch ak­tiv ist, ko­pie­ren Sie die Dat­en in die ers­te Zei­le unter­halb der let­zten (unge­filterten) Zei­le der Dat­en in Spal­te 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

An­mer­kung: Die bei­den an­de­ren Möglichkeit­en (unter­halb der ge­fil­ter­ten Dat­en in Spal­te A ste­hen weit­ere Dat­en) fol­gen in Kür­ze. Den Part mit der Mar­kie­rung allerd­ings schie­be ich et­was her­aus. Das geht nun wirk­lich gaaanz ein­fach per Hand: Mar­kie­ren, Strg und zu Ziel zie­hen. Fer­tig.

▲ nach oben …

B. Un­ter­halb der ge­fil­ter­ten Da­ten ste­hen wei­te­ren Wer­te

1. Die Da­ten sol­len nach D2 ko­piert wer­den.

Im Prin­zip geht es dar­um dass die ko­pier­ten Dat­en auf gle­ich­er Höhe einge­fügt wer­den, wo die unge­filterten Dat­en begin­nen. Das hat den Vor­teil, dass sie in Spal­te B schön oben ste­hen. Aber es kann dur­chaus sein, dass durch die aus­ge­blende­ten Zei­len nur ein Teil der ge­fil­ter­ten und ko­pier­ten Wer­te sicht­bar ist. Wird der Fil­ter in Spal­te A ent­fer­nt, sind auch die kom­plet­ten Dat­en in Spal­te D sicht­bar. Das ha­ben Sie bere­its erkan­nt.

Hier gibt es nun eine weit­ere Beson­der­heit bzw. Erschw­er­nis: In Spal­te A, wo die zu kopieren­den Dat­en ste­hen, muss die let­zte Daten­zeile des rel­e­van­ten Bere­ichs an­ders als ge­wohnt ge­sucht bzw. gefun­den wer­den. Nor­maler­weise wird (wie auch im Bei­spiel oben) mit ein­er Stan­dard­formel die let­zte be­leg­te Zei­le der Spal­te bes­timmt. Da hier aber der auszuw­er­tende, also der zu fil­ternde Bere­ich we­gen der dar­un­ter befind­lichen weit­eren Dat­en klein­er ist als die nor­mal berech­nete let­zte Zei­le, kann das nicht grei­fen.

Prinzip­iell gibt es hier zwei Lösungsan­sätze: Sie mar­kie­ren den entsprechen­den Daten­bere­ich per Hand oder aber Sie las­sen das Ex­cel ma­chen. Im zweit­en Fall muss min­destens 1 lee­re Zel­le unter­halb des zu fil­tern­den Daten­bere­ichs sein. Außer­dem darf bei den auszuw­er­tenden Dat­en (hier in Spal­te A) kei­ne Leer­zei­le sein. – Die zwei­te Zei­le im Code ist ein Kom­men­tar, der kurz dar­auf hin­weist, wie der Ein­satzz­weck ist.

Da die­se Vari­ante recht sel­ten ge­nutzt wird und das Prin­zip dem oben beschriebe­nen gle­ich ist, fol­gen hier der Codes für die ver­schiede­nen Möglichkeit­en ohne vie­le 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 je­dem Fall hin­haut.  Mit et­was we­ni­ger Code geht es aber auch fast im­mer:

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 an­hand der Bei­spie­le ge­wiss selb­st erar­beit­en kön­nen. Den zusam­menge­fassten Code kön­nen Sie hier als Text-Da­tei (*.zip-ge­packt) herun­ter­laden und nach Be­darf in Ihr Pro­jekt ein­binden.

▲ nach oben …

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

Hat Ih­nen der Bei­trag ge­fal­len?
Er­leich­tert die­ser Bei­trag Ihre Ar­beit?

Dann wür­de ich mich über ei­nen Bei­trag Ihrer­seits z.B. 3,00  freu­en … (← Klick mich!)

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.