Alphabetisch sortieren

[Michael Arriens]

Hallo liebe Excelfreunde,

ich möchte Werte in zwei Spalten, die nebeneinander liegen, per VBA alphabetisch sortieren.

Leider sind einige Zellen ohne Werte, sodass nach dem Sortieren die ersten Zeilen leer sind und die zu sortierenden Werte erst einige Zeilen später beginnen.

Wie bekomme ich es hin, dass die leeren Zellen am Ende der Liste erscheinen und trotzdem von A bis Z sortiert wird?

Code:

Sub alphabetisch_sortieren()
'
' alphabetisch_sortieren Makro
'

'
   Range("X2:Y1001").Select
   ActiveWorkbook.Worksheets("Alphabetische Liste Tische").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Alphabetische Liste Tische").Sort.SortFields.Add _
       Key:=Range("U2:U1001"), SortOn:=xlSortOnValues, Order:=xlAscending, _
       DataOption:=xlSortNormal
   With ActiveWorkbook.Worksheets("Alphabetische Liste Tische").Sort
       .SetRange Range("U2:V1001")
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
End Sub

Ich bin leider nicht so fit mit VBA, deshalb behelfe ich mir indem ich den Macrorecorder nutze. Hier stoße ich natürlich an meine Grenzen und benötige etwas Unterstützung.





Viele Dank für eure Hilfe.

Michael
Office 2016

[WillWissen]

Hi Michael,

mal ganz unabhängig davon, ob du klassisch über die Menüleiste oder per VBA sortieren willst - ich kann dein Problem aktuell nicht nachvollziehen. Hier mal eine (Beispiel)Liste, die nach Spalte A sortiert werden soll.

Originalzustand:

Tabelle1

	A	B
1	Text	Zahlen
2
3	bbb	111
4	ttt	222
5	qqq	444
6	aaa	777
7
8	ggg	333
9
10	kkk	555
11	eee	666

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Und hier nach A sortiert:

Tabelle1

	A	B
1	Text	Zahlen
2	aaa	777
3	bbb	111
4	eee	666
5	ggg	333
6	kkk	555
7	qqq	444
8	ttt	222
9
10
11

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Die leeren Zellen sind nicht am Anfang!

[RPP63]

Hallo auch von mir!
Du sortierst die Spalten X:Y nach dem Sortierschlüssel U 
Das kann ja nicht vernünftig funktionieren!
Nimm entweder X oder Y, dann klappt es auch mit den leeren Zellen.

Gruß Ralf

[Michael Arriens]

Hallo auch von mir!
Du sortierst die Spalten X:Y nach dem Sortierschlüssel U 
Das kann ja nicht vernünftig funktionieren!
Nimm entweder X oder Y, dann klappt es auch mit den leeren Zellen.

Gruß Ralf

Hallo Ralf,


vielen Dank.

Ich habe den Fehler sofort korrigiert, aber es bleibt dabei. Die ersten Zeilen bleiben leer.

Kann es daran liegen, dass ich aus verformelten Zellen nur die Werte hierhin kopiere und dann versuche zu sortieren?

Ich bin etwas ratlos, denn in einer neuen Datei funktioniert es auch bei mir tadellos. Ich habe auch schon in diesem Forum nach ähnlichen Fragen gesucht, aber leider nichts gefunden.
Hast du noch eine Idee?

[WillWissen]

Hi Michael,

anonymisiere doch bitte mal deine Datei und lade sie hier hoch. Nur so kann nachvollzogen werden, an was es liegen könnte - alles andere ist ein Stochern im Nebel.

Hier ist beschrieben, wie du Dateien hohladen kannst.

[Kl@us-M.]

Hi Michael,

selbst wenn in Spalte A "Lücken" sind und in Spalte B nicht, funktioniert das Sortieren (vorausgesetzt, Du sortierst die richtige Spalte):

Arbeitsblatt mit dem Namen 'Vorher'
	A	B
1	Text	Nr.
2	h	1
3	c	2
4		3
5	a	4
6	b	5
7		6
8	f	7
9	e	8
10		9
11		10
12	d	11
13	g	12

Arbeitsblatt mit dem Namen 'Nachher'
	A	B
1	Text	Nr.
2	a	4
3	b	5
4	c	2
5	d	11
6	e	8
7	f	7
8	g	12
9	h	1
10		3
11		6
12		9
13		10

[Michael Arriens]

Hi Michael,

anonymisiere doch bitte mal deine Datei und lade sie hier hoch. Nur so kann nachvollzogen werden, an was es liegen könnte - alles andere ist ein Stochern im Nebel.

Hier ist beschrieben, wie du Dateien hohladen kannst.

Hallo Günter,



vielen Dank für deine Geduld.
Ich hoffe, dass du meinen Tabellenblattausschnitt deuten kannst. Ich habe es mal auf 10 Zeilen reduziert und mit den Codes gearbeitet.

Code:

Sub alphabetisch_sortieren()
'
' alphabetisch_sortieren Makro
'

'
   Range("U2:V10").Select
   ActiveWorkbook.Worksheets("Alphabetische Liste Tische").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Alphabetische Liste Tische").Sort.SortFields.Add _
       Key:=Range("U2:U10"), SortOn:=xlSortOnValues, Order:=xlAscending, _
       DataOption:=xlSortNormal
   With ActiveWorkbook.Worksheets("Alphabetische Liste Tische").Sort
       .SetRange Range("U2:V10")
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
End Sub

Wo ist der verdammte Fehler?

[chris-ka]

Hi,

Wo ist der verdammte Fehler?

Du hast sicherlich noch mehr Code in der Mappe :)
Ich vermute mal das du die Formeln kopierst und als Inhalt einfügst....., habe ich richtig geraten?
Bei wennfehler hast du als Eintrag "" wird jetzt die Formel kopiert ist "" nicht gleich NIX..., darum werden diese Zellen sortiert.
Da ich nicht deinen Code kenn, auch keine Datei vorhanden ist.., ändere mal die Formel auf
=WENNFEHLER(SVERWEIS(ZEILE(A1);$J:$O;2;0);"ZZ")

und füge diese Zeile an deinen Code an:

Code:

        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("U2:U10").Replace "ZZ", "", xlWhole

[Michael Arriens]

Hi,

Du hast sicherlich noch mehr Code in der Mappe :)
Ich vermute mal das du die Formeln kopierst und als Inhalt einfügst....., habe ich richtig geraten?
Bei wennfehler hast du als Eintrag "" wird jetzt die Formel kopiert ist "" nicht gleich NIX..., darum werden diese Zellen sortiert.
Da ich nicht deinen Code kenn, auch keine Datei vorhanden ist.., ändere mal die Formel auf
=WENNFEHLER(SVERWEIS(ZEILE(A1);$J:$O;2;0);"ZZ")

und füge diese Zeile an deinen Code an:

Code:

        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("U2:U10").Replace "ZZ", "", xlWhole

Hi Chris,
vielen Dank für deine schnelle Reaktion. Das ist ein schöner Trick, nur leider habe ich jetzt am Ende lauter ZZ stehen. Du hast recht mit deiner Annahme, dass ich aus den Formeln nur die Werte kopiere, die ich dann sortieren möchte. Im Grunde möchte ich die Spalten Q und R durch Macros gestalten.
Die gesamte Datei ist etwas umfangreicher mit etwa 90 Tabellenblättern und unzählige kleinen Codes.
Der Einfachheit halber habe ich aus der Sortierung durch Formeln die Werte kopiert, um sie zu sortieren.
Die Sortierung mit Formeln ist nicht so genau, da ich die Buchstaben in Zahlen umwandle und leere (Blanks) nicht berechnet werden.
Ich habe mal den gesamten relevanten Bereich angehängt.
Ich hatte die naive Vorstellung, dass man den Code einfach erweitern könnte, um die leeren Zellen nicht mehr anzuzeigen. Es geht ja auch einigermaßen mit Formeln.
Schau dir mal das Tabellenblatt an. Ich hole mir die Daten aus einem anderen Tabellenblatt, um sie hier weiterzuverarbeiten.
Ich hoffe, ich konnte es etwas deutlicher veranschaulichen.

[RPP63]

Hi!
Zwei Bitten:
Zitiere nicht immer den vorherigen Beitrag.
Bitte den blauen Antworten-Button benutzen, nicht den grauen (den könnten die Mods eigentlich ersatzlos streichen oder ihn in "Mit Zitat antworten" umbenennen).
Der Html-Code befindet sich bei Dir in der Zwischenablage.
Den kannst Du einfach per Strg+v in Deine Antwort einfügen.
Siehe:

	A	B
2	Anfang	Ende
3	Januar 15

Formeln der Tabelle

Zelle Formel A3 =INDIREKT(TEXT((TEIL(ZELLE("dateiname";A1);FINDEN("]";ZELLE("dateiname"))+1;255)&"/0")-1;"MMMM")&"!B3")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Ralf