Kopiere nur befüllte Zellen, Skipblanks = true

  • Excel

Es gibt 10 Antworten in diesem Thema. Der letzte Beitrag () ist von peterfido.

    Kopiere nur befüllte Zellen, Skipblanks = true

    Guten Abend zusammen,

    ich bin seit 2 Tagen am verzweifeln.

    Ich versuche von einem Sheet B, worin eine Tabelle enthalten ist, eine komplette Zeile mit 10 Spalten zu kopieren und in ein anderes Sheet A, an entsprechender Stelle einzufügen.

    Kopieren klappt wunderbar, einfügen auch. Wenn in Sheet B nur nicht diese leeren Zellen wären.

    Ich sage meinem VBA Code, dass er die leeren Zellen nicht mit einfügen soll. Und zwar mit folgendem Code:

    Visual Basic-Quellcode

    1. Range("A1:J1").PasteSpecial Paste:=xlPasteValues, _
    2. Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    Jedoch funktioniert dies nicht. In Sheet A werden die befüllten Zellen durch die leeren Zellen in Sheet B einfach überschrieben.

    Habe ich etwa die SkipBlanks := True Eigenschaft falsch verstanden?

    Bin für jede Hilfe dankbar.

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „vbneuling60“ ()

    Hm. Kann ich grad nicht nachvollziehen. Ich habe eine Tabelle A, teilweise lückenhaft, und eine Tabelle B mit 8x5 Zellen komplett gefüllt. Wenn ich A in B mit dem genannten Code reinkopiere, werden alle Felder in B überschrieben, die in A nicht leer sind. Das ist doch Sinn und Ziel, richtig?

    Situation: Sheet 1 = Tabelle A, dort sind die Spalten A bis J komplett markiert. Sheet 2 = Tabelle B, in der dann folgendes Makro ausgeführt wird:

    Visual Basic-Quellcode

    1. ActiveSheet.Previous.Select
    2. Selection.Copy
    3. ActiveSheet.Next.Select
    4. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    Über die Verwendung von Selection und Co. ließe sich streiten, aber das ist hier ja auch nicht das Problemthema.



    Dieser Beitrag wurde bereits 5 mal editiert, zuletzt von „VaporiZed“, mal wieder aus Grammatikgründen.

    Häufig von mir verwendete Abkürzungen: CEs = control elements (Labels, Buttons, DGVs, ...) und tDS (typisiertes DataSet)
    Aufgrund spontaner Selbsteintrübung sind all meine Glaskugeln beim Hersteller. Lasst mich daher bitte nicht in den Spekulatiusmodus gehen.

    Dieser Beitrag wurde bereits 3 mal editiert, zuletzt von „VaporiZed“ ()

    Danke für deine Antwort. Wenn ich eine Testdatei erstelle, funktioniert es auch, wie bei dir. Aber genau in der Datei wo es funktionieren soll, geht es nicht.

    Kann es daran liegen, dass ich in der Tabelle B in den Zellen unabhängig davon ob leer oder befüllt jeweils eine Formel mit Sverweis habe? Aber ich sage ja, übernehme nur die Werte. Das dürfte eigentlivh nicht das Problem sein.
    Es geht beim Kopieren tatsächlich um Leerzellen, also nicht nur nix sichtbar, sondern auch nix hinterlegt. Wenn da ne Formel drinnen ist, wird das natürlich als relevanter Inhalt erkannt. Woher soll Excel auch wissen, was wichtiger ist. Aktueller Inhalt oder Formel? Da bliebe wohl nur das codeaufwendige Zelle-für-Zelle-Kopieren - zumindest AFAIK.
    Dieser Beitrag wurde bereits 5 mal editiert, zuletzt von „VaporiZed“, mal wieder aus Grammatikgründen.

    Häufig von mir verwendete Abkürzungen: CEs = control elements (Labels, Buttons, DGVs, ...) und tDS (typisiertes DataSet)
    Aufgrund spontaner Selbsteintrübung sind all meine Glaskugeln beim Hersteller. Lasst mich daher bitte nicht in den Spekulatiusmodus gehen.

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „VaporiZed“ ()

    Leider darf ich die Tabelle nicht anhängen, da vertraulich.
    Gibt es eine andere Lösung?

    Kann ich vielleicht per Makro die Zeile kopieren, in ein neues Sheet einfügen, sodass die Formeln verschwinden und dann die Zeile aus dem neuen Sheet kopieren und in Sheet A einfügen?

    Edit: Selbst wenn ich die Sverweis Formel rausmache, funktioniert es nicht. Echt unglaublich.


    Edit_2: Das ist mein Code:

    Visual Basic-Quellcode

    1. Sub FindeIDZumEinfuegen()
    2. Dim c As Variant
    3. Dim zeileEinfuegen As Long
    4. Dim suche As Variant
    5. Workbooks("MasterdateiInBearbeitung.xlsm").Sheets("Check").Activate
    6. Workbooks("MasterdateiInBearbeitung.xlsm").Sheets("Check").Select
    7. Dim rng As Range
    8. With ActiveSheet.UsedRange
    9. 'Setze Autofilter auf ja und evtl.
    10. '.AutoFilter Field:=20, Criteria1:="=evtl.", Operator:=xlOr, Criteria2:="=ja"
    11. .AutoFilter Field:=19, Criteria1:="=evtl.", Operator:=xlOr, Criteria2:="=ja"
    12. 'Durchlaufe die Schleife auf Basis des Autofilters
    13. For Each rng In ActiveSheet.Range("$A$2:$A$1803").SpecialCells(xlVisible)
    14. Workbooks("MasterdateiInBearbeitung.xlsm").Sheets("Check").Activate
    15. Workbooks("MasterdateiInBearbeitung.xlsm").Sheets("Check").Select
    16. rng.Select
    17. suche = rng.Value
    18. Workbooks("MasterdateiInBearbeitung.xlsm").Sheets("Overview").Activate
    19. Workbooks("MasterdateiInBearbeitung.xlsm").Sheets("Overview").Select
    20. 'Suche in Spalte F nach dem Wert
    21. With Sheets("Overview").Range("F:F")
    22. Set c = .Find(suche, LookIn:=xlValues, LookAt:=xlWhole)
    23. zeileEinfuegen = c.Row
    24. Sheets("Check").Activate
    25. Sheets("Check").Select
    26. 'Range(Cells(rng.Row, 1), Cells(rng.Row, 19)).Select
    27. Range(Cells(rng.Row, 1), Cells(rng.Row, 18)).Select
    28. Selection.Copy
    29. Sheets("Overview").Select
    30. 'Sheets("Overview").Cells(zeileEinfuegen, 6).Select
    31. Range("F" & zeileEinfuegen & ":W" & zeileEinfuegen).Select
    32. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    33. 'Wenn c nicht gefunden wurde, Kopier Vorgang einleiten
    34. 'If c Is Nothing Then
    35. 'End If
    36. End With
    37. Next
    38. End With
    39. End Sub

    Dieser Beitrag wurde bereits 3 mal editiert, zuletzt von „vbneuling60“ ()

    Hallo peterfido,

    danke für den Hinweis.

    Hier die Tabellen mit unkritischen Daten.

    Die CopyPaste Funktion dazu lautet:

    Visual Basic-Quellcode

    1. Sub FindeIDZumEinfuegen()
    2. Dim c As Variant
    3. Dim zeileEinfuegen As Long
    4. Dim suche As Variant
    5. Workbooks("Test.xlsm").Sheets("Check").Activate
    6. Workbooks("Test.xlsm").Sheets("Check").Select
    7. Dim rng As Range
    8. With ActiveSheet.UsedRange
    9. 'Setze Autofilter auf ja und evtl.
    10. .AutoFilter Field:=19, Criteria1:="=evtl.", Operator:=xlOr, Criteria2:="=ja"
    11. 'Durchlaufe die Schleife auf Basis des Autofilters
    12. For Each rng In ActiveSheet.Range("$A$2:$A$1803").SpecialCells(xlVisible)
    13. Workbooks("Test.xlsm").Sheets("Check").Activate
    14. Workbooks("Test.xlsm").Sheets("Check").Select
    15. rng.Select
    16. suche = rng.Value
    17. Workbooks("Test.xlsm").Sheets("Overview").Activate
    18. Workbooks("Test.xlsm").Sheets("Overview").Select
    19. 'Suche in Spalte F nach dem Wert
    20. With Sheets("Overview").Range("F:F")
    21. Set c = .Find(suche, LookIn:=xlValues, LookAt:=xlWhole)
    22. zeileEinfuegen = c.Row
    23. 'MsgBox (zeileEinfuegen)
    24. Sheets("Check").Activate
    25. Sheets("Check").Select
    26. Range(Cells(rng.Row, 1), Cells(rng.Row, 19)).Select
    27. Selection.Copy
    28. Sheets("Overview").Select
    29. 'Sheets("Overview").Cells(zeileEinfuegen, 6).Select
    30. Range("F" & zeileEinfuegen & ":W" & zeileEinfuegen).Select
    31. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    32. 'Wenn c nicht gefunden wurde, Kopier Vorgang einleiten
    33. 'If c Is Nothing Then
    34. 'End If
    35. End With
    36. Next
    37. End With
    38. End Sub


    Ich hoffe, du siehst den Fehler, ich habe es aufgegeben.

    Vielen lieben Dank.
    Dateien
    • Test.xlsx

      (121,55 kB, 31 mal heruntergeladen, zuletzt: )
    Hallo,

    so wie es aussieht, ist für Excel eine Zelle im UsedRange nicht mehr Empty. Daher überspringt er die auch nicht.

    Workaroundvorschlag:

    Visual Basic-Quellcode

    1. Option Explicit
    2. Sub FindeIDZumEinfuegen()
    3. Dim c As Variant
    4. Dim zeileEinfuegen As Long
    5. Dim suche As Variant
    6. Dim wQuelle As Worksheet
    7. Dim wZiel As Worksheet
    8. Dim rng As Range
    9. Dim Spalte As Long
    10. Set wQuelle = Sheets("Check")
    11. Set wZiel = Sheets("Overview")
    12. With wQuelle.UsedRange
    13. .AutoFilter 'Filter löschen
    14. .AutoFilter Field:=19, Criteria1:="=evtl.", Operator:=xlOr, Criteria2:="=ja" 'Setze Autofilter auf ja und evtl.
    15. For Each rng In wQuelle.Range("$A$2:$A$1803").SpecialCells(xlVisible)
    16. suche = rng.Value
    17. With wZiel.Range("F:F")
    18. Set c = .Find(suche, LookIn:=xlValues, LookAt:=xlWhole)
    19. zeileEinfuegen = c.Row
    20. For Spalte = 1 To 19
    21. If wQuelle.Cells(rng.Row, Spalte).Value <> "" Then
    22. wZiel.Cells(zeileEinfuegen, Spalte + 5).FormulaR1C1 = wQuelle.Cells(rng.Row, Spalte).Value
    23. End If
    24. Next
    25. End With
    26. Next
    27. End With
    28. Set wQuelle = Nothing
    29. Set wZiel = Nothing
    30. End Sub

    Gruß
    Peterfido

    Keine Unterstützung per PN!
    Hallo,

    ich habe einfach im Direktfenster getestet:

    Visual Basic-Quellcode

    1. debug.Print isempty(cells(4,6))
    2. Falsch


    wobei die Zelle F4 leer ist. Excel meldet dann aber trotzdem falsch.

    Zeile26:
    Die Formel habe ich genommen um eine evtl. vorhandene Formel zu überschreiben.
    Gruß
    Peterfido

    Keine Unterstützung per PN!