Microsoft Excel

Herbers Excel/VBA-Archiv

Makrooptimierung

Betrifft: Makrooptimierung von: David
Geschrieben am: 08.08.2008 09:24:57

Hallo zusammen.

Ich bin mal wieder dabei, meine VBA-Kenntnisse auf die Probe zu stellen und zu versuchen, diese zu erweitern.

Ich habe eine aus SAP exportierte Tabelle, die nach bestimmten Vorgaben umgebaut werden muss. Das von mir geschriebene Makro funzt soweit auch zufriedenstellend, nur die Performance ist etwas mager. Mangels Kenntnis von Alternativen verwende ich an mehreren Stellen Schleifen, bei denen die ganze Tabelle abgearbeitet wird. Ich vermute mal, das eine oder andere davon könnte ich auch durch schnellere Befehle ersetzt werden. Dazu wäre ich für konstruktive Vorschläge dankbar. An den Stellen, wo ich Optimierungspotential sehe, habe ich Kommentare eingefügt:

Sub umbauen()

Application.ScreenUpdating = False
Dim last As Integer
last = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

--'Wenn in einer Zeile der ersten Spalte der Text erscheint,
--'soll diese und die folgenden 11 Zeilen gelöscht werden
--'geht das auch einfacher?
For i = 1 To last
    If Cells(i, 1) = "ZPP00054" Then
        Range(Cells(i, 1), Cells(i + 11, 1)).EntireRow.Delete
    End If
Next

Range("s:s").EntireColumn.Delete
...
Range("a:a").EntireColumn.Delete

Range("F1:K1").Cells.Delete

--'Es sind immer wieder Leerzeilen vorhanden, diese werden hier gesucht und gelöscht
For i = last To 1 Step -1
    If Range("F" & i).Value = "" Then Range("F" & i).EntireRow.Delete
Next

Dim lastused As Integer
lastused = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row

--'in den ersten 5 Spalten steht in unregelmäßigen Abständen ein Text/Wert.
--'in den Zeilen, in denen dieser nicht steht, soll der darüber liegende Text/Wert
--'eingetragen werden - kann man das auch kürzer schreiben?
For i = 2 To lastused
    For j = 1 To 5
        If Cells(i, j) = "" Then
            Cells(i, j) = Cells(i, j).Offset(-1, 0)
        End If
    Next
Next

--'ab hier wird sicher nichts mehr zu optimieren sein
Range("B:B").NumberFormat = "DD.MM.YYYY"
Range("A1").EntireRow.Insert
Cells(1, 1) = "order"
...
Cells(1, 11) = "%over/under usg"
Range("1:1").Font.Bold = True
Range("A1:K" & lastused).AutoFilter
Range("A:K").Columns.AutoFit
Application.ScreenUpdating = True
End Sub



Vielen Dank schon mal.

Gruß

David

  

Betrifft: Leerzeilen löschen von: backowe
Geschrieben am: 08.08.2008 09:33:59

Hallo David,

kannst Du auch so lösen.

'Es sind immer wieder Leerzeilen vorhanden, diese werden hier gesucht und gelöscht
Range("F1:F" & Cells(Rows.Count, "F").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Gruss Jürgen


  

Betrifft: AW: Leerzeilen löschen von: David
Geschrieben am: 08.08.2008 10:30:56

Danke, klappt.

Gruß

David


  

Betrifft: AW: Leerzeilen löschen nochschneller von: Daniel
Geschrieben am: 08.08.2008 12:01:32

Hi

noch schneller wird das Löschen der Leerzeilen, wenn du die Datei vorher nach diese Spalte sortierst, so daß die zu löschenden Zeilen als geschlossener Block zusammenstehen.
das ist nämlich schneller als das löschen von getrennten Zeilen.

wenn die originalsortierung der restlichen Zellen nicht verändert werden darf kann man sich mit einer Hilfsspalte behelfen, die eine Funktion enthält, die für die zu löschenden Zeilen ein Blank und für die andern Zeilen die Zeilen-Nr vergibt:
bspw

=Wenn(Z1="";"";Zeile())


nach dieser Hilfsspalte dann sortieren und mit .SPECIALCELLS(XLCELLTYPEBLANKS).ENTIREROW die zu löschenden Zeilen markiern und Löschen dürfte die schnellste Variante sein.

Gruß, Daniel


  

Betrifft: AW: Leerzeilen löschen nochschneller von: David
Geschrieben am: 08.08.2008 12:07:40

das geht leider nicht, da die Zeilen tatsächlich in der Reihenfolge bleiben müssen und ich auf die (temporäre) Hilfsspalte gern verzichten möchte.

Mittlerweile ist die Runtime auch recht gut, so daß eine weitere Optimierung nicht zwingend notwendig ist.

Trotzdem danke für den Tip.

Gruß

David


  

Betrifft: AW: Makrooptimierung von: yusuf
Geschrieben am: 08.08.2008 10:40:30

Hi,

eine Optimierung waere es die Leerzeilenlöschung ganz an anfang zu tun, damit jede weitere Schleife schonmal nicht so lange braucht um durchzulaufen.

Gruß
Yusuf


  

Betrifft: AW: Makrooptimierung von: David
Geschrieben am: 08.08.2008 11:02:13

Mhh, manchmal kommt man nicht auf die einfachsten Sachen. Damit hat sich eine der Schleifen auch schon erledigt, da diese Zeilen gleich mit gelöscht werden.

Bleibt noch der "Auffüllen"-Teil. Die Daten sehen so aus:
Tabellenblattname: 6zpp54 (15)

 

A

B

C

D

E

485

1969656

11.06.2008

13169

MARGHERITA

CHEESE

486

 

 

 

 

CRUST

487

 

 

 

 

PACKAGING

488

 

 

 

 

SAUCE

489

 

 

 

 

 

490

 

 

 

 

VEGETABLE



Hier soll jede leere Zelle mit dem Wert darüber aufgefüllt werden, das Ganze für alle Zeilen der Tabelle und die ersten 5 Spalten.

Hat da jemand noch 'ne zündende Idee?

Gruß

David


  

Betrifft: AW: Makrooptimierung von: David
Geschrieben am: 08.08.2008 11:03:21

Mir ist gerade aufgefallen, das ich das Wichtigste in meiner Antwort vergessen habe:

DANKE yusuf!

Gruß

David


  

Betrifft: AW: Makrooptimierung von: Daniel
Geschrieben am: 08.08.2008 12:07:42

Hi

auch das ist zu lang und viel zu langsam:

 For i = 2 To lastused
         For j = 1 To 5
             If Cells(i, j) = "" Then
                 Cells(i, j) = Cells(i, j).Offset(-1, 0)
             End If
         Next
     Next



kürzer und schneller ist dies:

with range("A2:E" & Lastused)
   .specialcells(xlcelltypeblanks).formulalocal = "=A1"
   .formula = .Value
end with



Gruß, Daniel


  

Betrifft: AW: Makrooptimierung von: David
Geschrieben am: 08.08.2008 12:12:27

Wow, das ist schnell.

Allerdings verstehe ich die Vorgehensweise nicht ganz. Das .formulalocal = "=A1"bezieht sich doch auf A1? Wieso funzt das trotzdem? Kannst du mir das erklären, damit ich das für die Zukunft anwenden kann?

Danke.

Gruß

David


  

Betrifft: AW: Makrooptimierung von: Rudi Maintaire
Geschrieben am: 08.08.2008 12:21:48

Hallo,
relativer Zellbezug.
Kommentiere doch einfach mal
.formula = .Value
aus und schau dir die eingefügten Formeln an.

Gruß
Rudi


  

Betrifft: AW: Makrooptimierung von: Daniel
Geschrieben am: 08.08.2008 13:09:10

Hi

das hängt davon ab, wie du die Formel eingibst.
gewünscht ist ja die eigentlich die Formel "nimm den Wert aus der Zeile eins drüber"
das ist, wenn die Formel in die die Zelle A2 eingetragen wird, eben in der A1-Bezugsartschreibweise "=A1"
wird eine Formel in mehrere Zellen geleichzeitig eingetragen, so muss die Formel für der erste Zelle geschrieben werden.

in der Z1S1-Schreibweise wäre es eindeutiger, da hier die Zelle, in die die Formel geschrieben wird, nicht für den Formeltext relevant ist:"=Z(-1)S"

in VBA hat man 4 möglichkeiten Formeln einzugeben:
Internationale oder Landessprache und Z1S1-Bezugsart oder A1-Bezugsart.
die VBA-Befehle dafür heissen dann:
.Formula
.FormulaLocal
.FormulaR1C1
.FormulaLocalR1C1

Gruß, Daniel


  

Betrifft: AW: Makrooptimierung von: David
Geschrieben am: 08.08.2008 13:14:13

Ok, hab's verstanden, danke. Was mir fehlte war die Info, dass auch in VBA die Bezüge angepasst werden, wenn ich mich von der Ausgangszelle weiter bewege.

Gruß

David


  

Betrifft: AW: korrektur von: Daniel
Geschrieben am: 08.08.2008 13:19:36

Hi
erstmal auf deine Frage:

die Formel bezieht sich immer auf die erste Zelle, die eingegeben wird, dh in diesem Fall die erste leere Zelle im angegebenen Zellbereich.

dabei fällt mir auf, daß der Code nur dann Richtig funktioniert, wenn die Zelle A2 leer ist.
ist die Zelle A2 nicht leer, dann wirds fehlerhaft.

um auch in diesem Fall immer den Wert aus der zelle 1 drüber zu nehmen, muss die Formel in der Z1S1-Bezugsart angebeben werden, der Code sieht dann so aus:

 with range("A2:E" & Lastused)
      .specialcells(xlcelltypeblanks).formular1c1local = "=Z(-1)S"
      .formula = .Value
   end with



Gruß, Daniel


  

Betrifft: AW: korrektur von: David
Geschrieben am: 08.08.2008 13:21:29

Ok, werd ich mit einbauen, auch wenn normalerweise die Zelle A2 tatsächlich leer ist.

Danke noch mal.

Gruß

David