Microsoft Excel

Herbers Excel/VBA-Archiv

Kopieren sichtbare Zellen b. ausgeblendeten Zeilen

Betrifft: Kopieren sichtbare Zellen b. ausgeblendeten Zeilen von: Hubert Cimander
Geschrieben am: 01.11.2014 00:16:54

Hallo und guten Abend

mein Problem ist folgendes:
ich habe in den Zellen A1,A2+A3 die Werte 1,2+3 stehen.
Jetzt blende ich die Zeile 2 aus.
Danach möchte ich die sichtbaren Zellen (A1+A3) nach Spalte B kopieren.
Wenn ich das aber mache (ich kenne die Eingrenzung auf "nur sichtbare Zellen"),
dann stehen die kopierten Werte 1+3 aber in den Zellen B1+B2 statt in B1+B3

Hat jemand eine Lösung für mich wie ich die kopierten nur sichtbaren Zellen
auch in die neuen freien nur sichtbaren Zellen reinbekomme ?
Freue mich über jeden Tipp.

Schönen Gruß
Hubert

  

Betrifft: Ich kann mir nicht vorstellen, dass es dafür ... von: Luc:-?
Geschrieben am: 01.11.2014 02:37:00

…eine manuelle 1stufige Copy&Paste-Lösung gibt, Hubert,
denn im Prinzip erzeugst du mit separater Auswahl der relevanten Zellen ja einen unzusammen­hängenden Bereich (in VBA-Termini eine Union), auch Mehrfach­Auswahl genannt, der nun mal keine Leer­Zeile enthält. Wo soll die zwischen Copy und Paste herkommen? Das wäre wohl nur mit VBA möglich!
Aber was hindert dich daran, alles zwischen­zu­kopieren, dann die im Original ausgeblendete Zeile zu löschen, das Ergebnis dann nochmals zu kopieren und am end­gültigen Ziel­ort einzufügen…?!
Alternativ kannst du natürlich auch jede sichtbare Zeile einzeln an den vorge­sehenen Zielort kopieren… ;->
Gruß, Luc :-?


  

Betrifft: AW: Kopieren sichtbare Zellen b. ausgeblendeten Zeilen von: Hajo_Zi
Geschrieben am: 01.11.2014 09:55:14

Hallo Hubert,

mache es mit Formel.


Tabelle1

 AB
11313
24141
31414
441 

verwendete Formeln
Zelle Formel Bereich N/A
B1:B3=WENN(Hoehe=0;"";A1)  
A4=B2  

definierte Namen  
Name Bezieht sich auf Tabelle Z1S1-Formel
Hoehe=ZELLE.ZUORDNEN(17;INDIREKT("ZS(-1)";FALSCH)) =GET.CELL(17,INDIRECT("ZS(-1)",FALSE))
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 18.20 einschl. 64 Bit




Tabelle1
 AB
11313
31414
4  

verwendete Formeln
Zelle Formel Bereich N/A
B1,B3=WENN(Hoehe=0;"";A1)  
A4=B2  

definierte Namen  
Name Bezieht sich auf Tabelle Z1S1-Formel
Hoehe=ZELLE.ZUORDNEN(17;INDIREKT("ZS(-1)";FALSCH)) =GET.CELL(17,INDIRECT("ZS(-1)",FALSE))
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 18.20 einschl. 64 Bit



GrußformelHomepage


  

Betrifft: so ist das sicherlich nicht gewollt, denn ... von: neopa C (paneo)
Geschrieben am: 01.11.2014 10:24:28

Hallo Hajo,

... wenn die Zeile wieder eingeblendet wird, steht in B2 auch wieder der Wert aus A2. Wenn das doch so gewollt sein sollte, dann könnte man anstelle der Excel4-Makrofunktion auch einfach auf TEILERGEBNIS() zurückgreifen.


Gruß Werner
.. , - ...


  

Betrifft: AW: Kopieren sichtbare Zellen b. ausgeblendeten Zeilen von: Daniel
Geschrieben am: 01.11.2014 11:28:55

Hi

Zunächst einmal wäre die Frage zu klären, was in Spalte B in den ausgeblendeten Zeilen steht.
Sind diese leer und sollen auch leer bleiben, so wäre am einfachsten die von Luc beschriebene Methode zu verwenden, nämlich erst alles zu kopieren, dann mit umgekehrter Logik zu filtern und in Spalte B die sichtbaren Zellen zu leeren.

Komplizierter wird's, wenn in Spalte B in den ausgeblendeten Zeilen Werte und Formatierungen stehen und diese erhalten bleiben müssen.
Dann hängt es davon ab, ob du in Spalte B nur die Werte aus A benötigst oder ob du vollständig kopieren willst, dh mit Formeln und Formaten.

Wenn du nur die Werte brauchst, dann kannst du auch einfach filtern und in die erste sichtbare Zelle (B1) die Formel =A1 eintragen und nach unten kopieren. In der gefilterten Liste werden die Formeln dann nur in die sichtbaren Zeilen eingefügt, die ausgeblendeten werden nicht bearbeitet.

Ein vollständiges Kopieren wird dann etwas umständlicher:

1. Hebe den Filter auf und trage in eine erste Hilfsspalte in alle Zellen die Formel =Zeile() ein.
2. Filtere und trage in eine zweite Hilfsspalt die Formel =Zeile() ein. Die Formel wird durch die Filterung nur in die sichtbaren Zeilen eingetragen.
3. Hebe die Filterung auf.
4. Kopiere beide Hilfsspalten und füge sie an gleicher Stelle als wert ein (Formeln fixieren)
5. Sortiere die Tabelle nach der zweiten Hilfsspalte. Die Zellen die von A nach B kopiert werden müssen, sollten jetzt lückenlos untereinander stehen.
6. Kopiere die Zellen von A nach B
7. Sortieren die Tabelle nach der ersten Hilfsspalten, um sie wieder in die originale Reihenfolge zu bringen.
8. Lösche die beiden Hilfsspalten.

Eine Makrolösung könnte so aussehen:

Dim A As Range
For Each A in columns(1).specialcells(xlcelltypevisible).Areas
   A.copy Destination:=A.Offset(0, 1)
Next


Gruß Daniel


  

Betrifft: AW: Kopieren sichtbare Zellen b. ausgeblendeten Zeilen von: Hubert Cimander
Geschrieben am: 02.11.2014 11:04:38

Hallo Luc, Hallo Hajo
Hallo Werner, Hallo Daniel

jetzt will ich aber endlich eine Rückmeldung bringen.
vielen vielen Dank für eure Vorschläge und Lösungsansätze.
Ich habe mir alles in Ruhe angesehen und durch den Kopf gehen lassen,
erst eínmal um alles auch richtig zu verstehen und dann ob es für meine Sache das Richtige wäre.
Das Zwischenkopieren habe ich dann ausgeschlossen, da mir die Chance eines Fehlers beim Löschen der im
Original ausgeblendeten Zeilen zu groß wäre und Hajo´s Vorschlag habe ich dann auch nicht genommen,
da in der Ausgangsspalte auch einiges mit Formeln steht und in der Zielspalte nur Werte ankommen sollen.
Ich bin dann bei VBA hängengeblieben und habe mir Daniels Vorschlag noch ein bisschen umgebaut.
Einmal von der Ausgangsspalte in die Zielspalte kopieren oder in der Zielspalte sich die Werte aus der
Ausgangsspalte holen.
Wobei die 2. Variante deutlich schneller ist.
Zu eurer Info hier mein umgebautes Endprodukt:

Variante 1

Dim A As Range
Application.ScreenUpdating = False
For Each A In Columns("HI").SpecialCells(xlCellTypeVisible).Areas
A.Copy
A.Offset(0, 2).PasteSpecial Paste:=xlValues
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True

Variante 2

Dim A As Range
Application.ScreenUpdating = False
For Each A In Columns("HK").SpecialCells(xlCellTypeVisible).Areas
A.Value = A.Offset(0, -2).Value
Next
Application.ScreenUpdating = True

Euch nochmals vielen Dank und ich finde es immer wieder Super, wie mir
in diesem Forum geholfen wird.

Schönen Gruß
Hubert


 

Beiträge aus den Excel-Beispielen zum Thema "Kopieren sichtbare Zellen b. ausgeblendeten Zeilen"