Office: VBA makro - sortieren von Zeilen in 3er Schritten

Hallo,

weil sich Anforderungen geändert haben muss ich eine umfangreiche Mappe mit vielen Makros überarbeiten und anpassen.
(ich bin schon weit gekommen, aber hier stecke ich fest)

In einem Blatt sollen die Zeilen alphabetisch nach der ersten Zelle sortiert werden.
Das kann ich.

Aber in diesem Blatt gehören immer drei Zeilen zusammen, bilden einen "Block". Deren Reihenfolge darf sich nicht ändern und sie müssen zusammen bleiben.
Leider sind die Einträge in den ersten Zellen dieser "Blocks" nicht in alphabetischer Reihenfolge.

In Spalte A werden immer Länder mit immer dem gleichen Zusatz stehen, die Anzahl der verschiedenen Länder wird wachsen.

Und so soll es aussehen:

/..........Spalte A........../
---------------------------

China FP Eing.
China FP Ausg.
China FP Diff.
Andorra FP Eing.
Andorra FP Ausg.
Andorra FP Diff.
Belgien FP Eing.
Belgien FP Ausg.
Belgien FP Diff.

usw.

Das Makro müsste also nach dem Ländernamen sortieren, aber nicht einzelne Zeilen sondern immer die drei zusammengehörenden verschieben:

/..........Spalte A........../
---------------------------

Andorra FP Eing.
Andorra FP Ausg.
Andorra FP Diff.
Belgien FP Eing.
Belgien FP Ausg.
Belgien FP Diff.
China FP Eing.
China FP Ausg.
China FP Diff.

usw.


Wie kann ich in so einem Makro festlegen das drei Zeilen als "Block" gehandhabt werden?

Das "normale" Sortieren sähe bei mir so aus:

Public Sub Länder_sortieren()

ActiveWorkbook.Worksheets("Länder").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Länder").Sort.SortFields.Add Key:= _
Range("A1:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortTextAsNumbers

With ActiveWorkbook.Worksheets("Länder").Sort
.SetRange Range("A1:AK10000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub



Ich hoffe das es irgendwie machbar ist und Ihr mir dabei helfen könnt.

 

Hallo Achiom,

warum nicht sortieren nach Spalte A und weiteren Spalten da steht doch 3x China?
Mal den Makrorecorder benutzen.

Gruß Hajo

 

Hallo Hajo,

wie ich nach Spalte A noch nach Spalte B usw. sortiere weiß ich (Add Key:=).

Hier möchte ich aber nur nach Spalte A sortieren, die anderen Spalten enthalten Zahlen.

Deshalb habe ich ja geschrieben:
"In Spalte A werden immer Länder mit immer dem gleichen Zusatz stehen"
und deshalb auch das:
/..........Spalte A........../
---------------------------
über die Beispiele gesetzt.

Der Ländername wird nicht allein in der Zelle stehen, sondern auch die Zusätze hinter den Ländernamen.

Leider sind aber:
Eing.
Ausg.
Diff.
nicht in alphabetischer Reihenfolge, also würde das makro mir die Reihenfolge ändern.
Und genau das gilt es zu vermeiden.

Ich hoffe mein Anliegen ist jetzt verständlicher geworden.

 

Hallo Achim,

das geht nicht.

Gruß Hajo

 

Danke für die Antwort Hajo.

Schade, hatte ich befürchtet.
Dann muss ich mir doch mit einer Hilfsspalte behelfen indem ich in Spalte B jeweils 1 - 2 - 3 oder a - b - c eintrage, die komplette Spalte ausblende und Spalte B als 2. Kriterium beim Sortieren nehme.

Verschiebt mir natürlich alles und ich muss in VBA noch mehr anpassen... (traurig guck).

 

Daran dachte ich auch, wollte aber zuerst Deine Antwort abwarten. Ich war mir nämlich auch nicht ganz sicher, ob ich Deine Frage richtig verstanden hatte.

Es hindert Dich doch niemand daran, die Hilfsspalte in IV (Spalte 256) zu legen. ;-)

Und: solche Hilfsspalten benutze ich häufiger, wenn ich nach mehreren Kriterien sortieren muß. Bei 3 Kriterien ist ja sonst sowieso Sense (zumindest bei meinem 2000er). So baue ich mir dann eine Hilfsspalte, in der ich die einzelnen Kriterien mit "&" zu einem SortKey zusammenknote und dann nur noch nach der Hilfsspalte sortieren muß.

Du könntest also in Spalte IU eine WENN()-Funktion ablegen, die je nach "Eingang", "Ausgang", "Differenz" eine 1, 2 oder 3 vergibt. In IV könntest Du dann mit "&" das Land und die Hilfsspalte verknoten und nach IV sortieren. Sollten weitere Kriterien dazu kommen, kannst Du das jederzeit problemlos erweitern, ohne daß sich im eigentlichen Arbeitsbereich was verschiebt.

Gruß, Michael

PS: Spalte IV bzw. pauschal das rechte Ende der Tabelle zu benutzen ist sicherlich nicht die beste Wahl! Das war jetzt nicht ganz ernst gemeint. Aber direkt rechts der Arbeitsdaten sollte normalerweise kein Problem sein, ggf. mit ein paar leeren Spalten dazwischen, um Platz für Erweiterungen zu lassen.

 

Hallo Michael

Touché.
Hm, aber ma so als DAU gefragt:
Erhöht das nicht die Speicherbelastung?
Ich mein, wenn ich Beispielsweise nur die ersten 20 Spalten benutze, dann ne Zeile kopiere und einfüge, den Kram sortiere oder sonstwas mit mache, werden dann tatsächlich immer alle 256 Spalten "abgearbeitet"?
Oder ist Excel intelligent genug zu merken das die ganzen Spalten hinten leer sind und getrost ignoriert werden können?
(Ausser natürlich ich arbeite mit UsedRange oder definiere den Bereich anderweitig.)

Ich frage deshalb, weil diese Datei mein allerstes Machwerk in VBA war (ca. halbes Jahr her).
Natürlich habe ich mich als absoluter Anfänger damit total übernommen, keine Frage, und vieles zu kompliziert oder zu speicherintensiv geschrieben. (war beim Arbeiten öfters zu merken und lästig)
Da ich aber nun eh die Datei komplett umkrempeln muss und nur bestimmte Abläufe und Funktionen erhalten bleiben, versuche ich natürlich die Sache diesmal besser zu machen. (soweit mein Wissen halt reicht)

Hört sich gut an, kannte ich noch nicht.
Danke das ich wieder etwas dazu lernen konnte.
Bin mir aber nicht sicher ob mir das die Sache vereinfacht, muss ich mir den Code nochmal anschauen.

Und wieso nicht "die beste Wahl"?
(s.o.)

Erweitert wird der Bereich nach rechts hoffentlich nicht.
Das besagte Blatt ist erstmal bis Dezember 2011 ausgelegt.
Bis dahin sollten wir eigentlich eine professionelle Software haben die diese Aufgaben miterledigt.
Und falls nicht wäre die Erweiterung um ein-zwei Jahre relativ einfach.


Danke für Deine Antwort.

 

Die Speicher-Belastung ja, die Rechen-Belastung eher nicht so sehr. Bei beiden ist aber wahrscheinlich mit einigermaßen modern ausgestatteten Rechnern kein großer Unterschied zu merken.

Es gibt Operationen, die bedeutend schlimmer sind: bedingte Formatierung und Matrix-Formeln. Aber auch dabei ist immer die Frage, wie groß der zu bearbeitende Bereich ist. Bei einigen 100 Zeilen mag das noch gehen, ab 10.000 Zeilen sieht das schon anders aus. Ich kenne das aus leidlicher Erfahrung: Ich hab hier u.a. eine Datei mit 17.000 Zeilen und bedingter Formatierung in 5 Spalten. Eine andere Datei hat 33.000 Zeilen und eine Spalte mit Matrix-Formeln. Da vergehen schon einige Sekunden nach einer Wert-Änderung oder einer neuen Auswahl im Auto-Filter.

Nein, ganz so schlimm ist es nicht. intern verwaltet Excel, grob gesagt, eine Matrix, mit der es weiß, in welchen Zellen ggf. Neuberechnungen durchgeführt werden müssen. Dabei ist es dann ziemlich egal, ob das in Spalte 25 oder in Spalte 256 passieren muß. Leere Zellen und konstante Werte sind dabei z.B. nicht in dieser To-Do-Liste.

Bingo! Damit hast Du nämlich genau einen Punkt erwischt, der gegen das Verwenden der letzten Spalte spricht. Und nicht nur per VBA ist das unglücklich, auch der Anwender könnte leicht genervt sein: Wenn man beim Drücken von Strg-Ende plötzlich irgendwo rechts unten im Nirwana steht und erst wieder ganz nach links zurück muß, ist das nicht besonders nett.

Das ist eine gängige Praxis in der Programmierung. Wer einmal versucht hat, den eigentlich ganz simplen Bubble-Sort (ca. 10 Zeilen Source) mit mehr als einem Kriterium hinzubekommen, wird sich daran erinnern.

Und nicht nur zum Sortieren ist das ganz hilfreich. Ich bastel sowas auch gerne mal als Hilfsspalte für den Auto-Filter mit ein. Ich hab hier z.B. eine Datei, bei der eine Art "Artikelnummer" vorkommt. In dieser Nummer ist ein Code, bei der bestimmte Stellen entsprechende Bedeutung haben. Eine häufige Filterung ist auf der 3. Stelle (die Zahl ist 9-stellig) nach "0" oder "3". Jeder nicht sehr versierte Anwender verzweifelt in dem Moment an dem benutzerdefinierten Filter. Eine Hilfsspalte mit TEIL() richtet deren Weltbild wieder gerade... ;-)

Wie oben geschrieben: Den UsedRange zerhagelt es damit ordentlich. Außerdem könnte ich mir vorstellen, daß es die Dateigröße etwas aufbläht, was aber i.a. weniger ein Problem darstellen sollte. Auch die Arbeitsspeicher-Last könnte etwas steigen. Das sind jetzt aber mehr Vermutungen aus dem Bauchgefühl heraus. Mir wäre es von der Bearbeitung her auch einfach zu unbequem, immer so weit nach rechts rollen zu müssen.

Wenn man etwas konservativer ist und früher noch Arbeitsspeicher gespart hat, indem man sich wohl überlegt hat, ob man "byte" oder "integer" als Datentyp benutzt, dann ist man da auch heute noch etwas sparsamer mit den Resourcen. Aber andererseits heute, wo jede Grafikkarte ein Vielfaches an Grafikspeicher ggü. dem Arbeitsspeicher in den damaligen Rechner hat, bin ich schon auch öfters mal auf dem "5e gerade sein lassen" Trip. ;-) (Mein damaliger "High-End" 486 DX/100 VLB hatte, wenn ich mich recht entsinne, 32 MB Arbeitsspeicher. Das bringt meine jetzige "Standard" Grafikkarte mit 512 MB nicht mal mehr zum Grinsen.)

Gruß, Michael

 

Hallo Michael,

danke für Deine ausführliche Antwort.
Habe wieder ein bisschen mehr über Excel erfahren.

"Bubble-Sort" ... wie gut das es Wikipedia gibt. ;O)

Deine Verknüpfungsmethode habe ich nicht gebraucht, aber der Beitrag ist abgespeichert und bei Bedarf krame ich dieses Wissen wieder hervor.

Zu meiner Eingangsfrage:
Anstatt einer habe ich nun drei Spalten.
Die Einträge habe ich auf Spalte A & C verteilt, Spalte B benutze ich als Hilfsspalte zum Sortieren (ausgeblendet).
Sortiert wird nach Spalte A und Spalte B.

Ohne die Einträge auf 2 Spalten zu verteilen klappte die Sortierung selbst mit der Hilfsspalte nicht.
Im Nachhinein eigentlich logisch.

Die dadurch nötigen Änderungen in meinem Code hielten sich zum Glück sehr in Grenzen.
Hatte fast alles eh schon über Variablen laufen die sich automatisch anpassen wenn Spalten hinzugefügt werden.


Und so klappts auch mit der Nachbarin (äh, Sortierung):