Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
Einfügen ohne ausgeblendete Zeilen/Spalten Performancefrage.
zurück: Bestimmte Zeilen löschen, ca. 192.000 Zeilen!!! weiter: Tabelle filtern nach Vorgabe aus Dropdownfeld Unbeantwortete Beiträge anzeigen
Neues Thema eröffnen   Neue Antwort erstellen     Status: Offen Facebook-Likes Diese Seite Freunden empfehlen
Zu Browser-Favoriten hinzufügen
Autor Nachricht
NomekSan
Google-VBA-Kurs Besucher


Verfasst am:
28. Apr 2014, 23:22
Rufname:

Einfügen ohne ausgeblendete Zeilen/Spalten Performancefrage. - Einfügen ohne ausgeblendete Zeilen/Spalten Performancefrage.

Nach oben
       Version: Office 2007

Hi,

ich habe ein Makro geschrieben, welches eine kopierte Range aus Tabelle A in Tabelle B kopiert und zwar in der Form, dass hierbei ausgeblendete Zeilen und Spalten nicht berücksichtigt werden. Diese bleiben also unbeschrieben. Dies ist notwendig, da Daten zwischen verschiedenen ähnlichen Tabellen kopiert werden müssen, diese aber Teilweise eine unterschiedliche Anzahl an Spalten haben. Das Makro deckt also den Fall "Zieltabelle hat mehr Spalten als Quelltabelle" ab. Die Funktionalität, dass ausgeblendete Spalten ausgelassen werden ist im Grunde hierbei nicht notwendig, erweitert aber den Anwendungsbereich.

Das Makro nimmt also eien kopierte Tabelle asl Text aus der Zwischenablage und splittet zunächst diesen langen String in die einzelnen Zeilen auf und speichert diese in einem Array. Für jede Zeile wird dann der jeweilige Eintrag in diesem Array wieder ausgesplittet wodurch ein Array mit den Zelleinträgen dieser Zeile entsteht. Nun werden diese Einträge nacheinander in die Zielzellen eingefügt und die ausgeblendeten übersprungen.

Ich habe einiges an Dingen versucht um die Geschwindigkeit zu erhöhen, die größten Erfolge haben dabei das ausschalten der automatischen Berechnung und der Events (Sheet_Change-Ereignis) gebracht.

Nun ist meine Frage an jene, die sich wirklich auskennen, ob diese Funktionalität auch eleganter, kürzer und vor allem schneller umgesetzt werden kann. Wie man sieht, habe ich ebenfalls eine Statusbar mit Ladebalken und Restzeitberechnung eingefügt. Der Grund dafür ist, dass der Durchlauf für eine Tabelle mit 350.000 Zellen in etwa 2 minuten dauert. Einw eiteres Problem ist, dass nach jedem Durchlauf, der Arbeitsspeicher, den Excel braucht, gefüllt wird und sich nur zum Teil wieder leert. Nach einiger Zeit lassen sich somit immer geringere Datenmengen verarbeiten.

Als Idee schwirrt mir im Kopf herum, dass man jeweils ganze Ranges direkt beschreiben könnte. Dafür müssten aber im Vorfeld die Positionen der ausgeblendeten Spalten bekannt sein, die Ranges entsprechend dieser Positionen variieren und die Aufteilung eines Zeilenstrings ebenso in den richtigen Abschnitten erfolgen. Hier hapert es bei mir gerade aber vielleicht denke ich ja auch in die falsche Richtung.

Im Grunde funktioniert das Makro und die Datenmengen sind meinstens nicht größer als 35000 Zellen was aber immer noch etwa 15 Sekunden dauert. Dennoch möchte ich natürlich immer gerne die optimalste Lösung wissen und somit auch meinen VBA-Lösungsfundus erweitern.

Vielleicht hat ja jemand eine feine Idee dafür Smile

Kleine Anmerkung:
Die Variable grenze, welche hier nur als Konstante verwendet wird, hat für das Makro so wie es ist keine Funktion. Im Schritt danach erfolgt eine Abfrage ob der einzufügende Bereich innerhalb dieser grenze platziert werden kann. Dies ist für den tatsächlichen Anwendungsfall wichtig.



Code:

Sub pasteonlyvisibleOhneUF()

On Error GoTo errhdlr
Application.EnableCancelKey = xlErrorHandler
Set clipboard = New DataObject
Dim copiedrange As String
Dim splitrows
Dim splitcells
Dim actC As Range, pasterange As Range
Dim i As Integer
Dim grenze As Integer
Dim a As Long
Dim t As Date, restzeit As Date
Dim timediff As Double
Dim isum As Integer
Dim progress As Double, partmax As Integer, progsum As Double, progmax As Integer, cellmax As Integer
Dim showstatusbar As Boolean


grenze = 11000

clipboard.getfromclipboard
copiedrange = clipboard.gettext

showstatusbar = True
progmax = 1
With Application
.Cursor = xlWait
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With



Set actC = ActiveCell
splitrows = Split(copiedrange, Chr(13))
partmax = UBound(splitrows)
If UBound(splitrows) > grenze - ActiveCell.Row + 1 Or ActiveCell.Row > grenze Then
MsgBox "Es fehlen " & (UBound(splitrows) - grenze - ActiveCell.Row + 1) & " Zeile/n."
GoTo ende
End If

i = 0
For k = ActiveCell.Row To grenze
t = Now
If Rows(k).EntireRow.Hidden = False Then

splitcells = Split(splitrows(i), Chr(9))
cellmax = UBound(splitcells)
If Not cellmax = -1 Then
Set pasterange = Range(Cells((k), actC.Column), ActiveSheet.Cells((k), 16384))
   j = 0
    For Each x In pasterange
    If x.EntireColumn.Hidden = False Then
        x.Value = splitcells(j)
        j = j + 1
    If j > cellmax Then Exit For
    End If
    Next
    End If
   
If i < partmax Then
i = i + 1
Else
Exit For
End If

isum = isum + 1
End If
   

'################################-STATUSBAR-#####################################################################
'UBound(splitrows)=imax
'Restdauerberechnung: (timediff / i) * (imax - i)
'Timediff ist die aufsummierte differenz zw. der zeit am ende eines durchlaufes und der am anfang

'progsum=progress am Ende einer äußeren Schleife
't=Now am Anfang einer inneren Schleife
'partmax = Anzahl Durchläufe innere Schleife (am Anfang dieser setzen)
'progmax = Anzahl Durchläufe äußere Schleife
'isum= Aufsummierte Anzahl an inneren Schleifendurchläufen
'Deklarationen
'Dim t As Date, restzeit As Date
'Dim timediff As Double
'Dim isum As Integer
'Dim progress As Double, partmax As Double, progsum As Double, progmax As Double
'Dim showstatusbar as boolean

'Für Statusbar OHNE äußere Schleife:
'progsum = 0
'progmax = 1

If i Mod 50 = 0 Then DoEvents 'Führt DoEvents alle 50 Durchläufe aus. Bei jeden Durchlauf ist ESC kaum möglich.
If showstatusbar Then
timediff = timediff + (Now - t): restzeit = (timediff / isum) * ((partmax * progmax) - isum): progress = progsum + (i / (partmax)) * (100 / progmax)
Application.StatusBar = String(CInt((progress)), "|") & String(CInt(100 - ((progress))), ":") & "|" & _
"  [" & Format(progress, "000.0") & " %]" & "   Restdauer: " & Format(restzeit, "hh:mm:ss") & "    Zum Abbrechen ESC halten"
End If

'################################-STATUSBAR-#####################################################################
Next


progsum = progress
'Next für eine äußere Schleife
ende:
With Application
.Cursor = xlDefault
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.StatusBar = False
End With

Exit Sub
errhdlr:
With Application
.Cursor = xlDefault
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.StatusBar = False
End With
Select Case Err
Case 18
Case -2147221404
MsgBox "Keine Daten in der Zwischenablage!", vbCritical
Case -2147221404
MsgBox "Die Menge an Daten ist zu groß!", vbCritical
Case Else
MsgBox Err & "   " & Err.Description
Debug.Print Err & "   " & Err.Description
End Select
Exit Sub
End Sub


Kleiner Nachtrag:
Ich habe natürlich die erstellten Objekte am Ende nicht entfernt. Das könnte schonmal den vollen Arbeitsspeicher erklären wobei diese Objekte ja ach wieder überschrieben werden in einem neuen Durchlauf. Der Arbeitsspeicher läuft mir trotzdem voll. Naja aber ein Fehler ist es definitiv.
Phelan XLPH
Fortgeschritten


Verfasst am:
29. Apr 2014, 08:14
Rufname: Phelan

AW: Einfügen ohne ausgeblendete Zeilen/Spalten Performancefr - AW: Einfügen ohne ausgeblendete Zeilen/Spalten Performancefr

Nach oben
       Version: Office 2007

Hallo,

sollen nur die Werte kopiert, oder darf Alles (Formatierung, usw.) kopiert werden?

_________________
Was vorstellbar ist, ist auch machbar. - Albert Einstein
NomekSan
Google-VBA-Kurs Besucher


Verfasst am:
29. Apr 2014, 12:45
Rufname:

AW: Einfügen ohne ausgeblendete Zeilen/Spalten Performancefr - AW: Einfügen ohne ausgeblendete Zeilen/Spalten Performancefr

Nach oben
       Version: Office 2007

Formatierung usw. wäre sogar wünschenswert so lange die ausgeblendeten übersprungen werden.
Phelan XLPH
Fortgeschritten


Verfasst am:
29. Apr 2014, 14:59
Rufname: Phelan

AW: Einfügen ohne ausgeblendete Zeilen/Spalten Performancefr - AW: Einfügen ohne ausgeblendete Zeilen/Spalten Performancefr

Nach oben
       Version: Office 2007

Hallo,

dann dürfte das hier langen:

Code:
Sub pasteonlyvisibleOhneUF()
    Tabelle1.Cells(1, 1).CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Tabelle2.Cells(1, 1)
End Sub

_________________
Was vorstellbar ist, ist auch machbar. - Albert Einstein
NomekSan
Google-VBA-Kurs Besucher


Verfasst am:
30. Apr 2014, 08:11
Rufname:

AW: Einfügen ohne ausgeblendete Zeilen/Spalten Performancefr - AW: Einfügen ohne ausgeblendete Zeilen/Spalten Performancefr

Nach oben
       Version: Office 2007

Hi, sorry für die späte Antwort, viel zu tun gerade.
Diese Variante deckt leider nur den Fall ab, dass die Quelltabelle mmehr Spalten hat als die Zieltabelle. Ich möchte von einer Tabelle mit x Spalten Inhalte in eine Tabelleit x+y Spalten einfügen wobei die y Spalten irgendwo verteilt sind. Da hilft es nicht aus der Quelltabelle nur die sichtbaren zu kopieren. Es geht um das Einfügen.

Trotzdem Danke schon mal Smile
Neues Thema eröffnen   Neue Antwort erstellen Alle Zeiten sind
GMT + 1 Stunde

Diese Seite Freunden empfehlen

Seite 1 von 1
Gehe zu:  
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen

Verwandte Themen
Forum / Themen   Antworten   Autor   Aufrufe   Letzter Beitrag 
Keine neuen Beiträge Excel Formeln: Eine Zeile am Ende mit einem CommandButton einfügen 0 chico24 884 11. Jan 2005, 10:29
chico24 Eine Zeile am Ende mit einem CommandButton einfügen
Keine neuen Beiträge Excel Formeln: Spalten Sortieren 23 Fireball8 3108 07. Jan 2005, 18:40
fridgenep Spalten Sortieren
Keine neuen Beiträge Excel Formeln: Zeilen mit doppelten Werten löschen 6 Sebowsky 1208 06. Jan 2005, 22:26
Sebowsky Zeilen mit doppelten Werten löschen
Keine neuen Beiträge Excel Formeln: Erster Wert aus mehreren Spalten 4 Gast 1922 03. Dez 2004, 10:52
Arnim Erster Wert aus mehreren Spalten
Keine neuen Beiträge Excel Formeln: Vor- und Nachname in 1 Spalte in 2 Spalten aufteilen 2 Gast 2920 26. Nov 2004, 11:48
Gast Vor- und Nachname in 1 Spalte in 2 Spalten aufteilen
Keine neuen Beiträge Excel Formeln: ZÄHLENWENN bei 2 Suchkriterien in wechselnden Spalten 10 Rebecca Nau 1909 12. Nov 2004, 11:26
Arnim ZÄHLENWENN bei 2 Suchkriterien in wechselnden Spalten
Keine neuen Beiträge Excel Formeln: Werte aus 3 Spalten nebeneinander in 1 Spalte 2 Aloha 2697 02. Nov 2004, 23:31
Gast Werte aus 3 Spalten nebeneinander in 1 Spalte
Keine neuen Beiträge Excel Formeln: Wenn-Bedingung mit mehreren Spalten und Zeile 6 Lusie 2819 02. Nov 2004, 15:57
Lusie Wenn-Bedingung mit mehreren Spalten und Zeile
Keine neuen Beiträge Excel Formeln: Druckbereich:nur einzelne Spalten als Druckbereich markieren 3 mufty001 1912 21. Okt 2004, 09:24
mufty001 Druckbereich:nur einzelne Spalten als Druckbereich markieren
Keine neuen Beiträge Excel Formeln: gegeben 2 Spalten/ ges. wie oft Wert aus Spalte 1 mit Wert a 4 Stefan B. 1310 21. Okt 2004, 08:06
Stefan B. gegeben 2 Spalten/ ges. wie oft Wert aus Spalte 1 mit Wert a
Keine neuen Beiträge Excel Formeln: Buchstaben einfügen 2 ICH 774 04. Okt 2004, 14:31
Gast Buchstaben einfügen
Keine neuen Beiträge Excel Formeln: Spalten kombinieren??? 3 fridgenep 1789 23. Sep 2004, 21:22
fridgenep Spalten kombinieren???
 

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: PHP JavaScript