Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Zellen sortieren + leere Zellen ignorieren

Zellen sortieren + leere Zellen ignorieren
23.01.2007 17:36:07
Markus
Ich habe das Problem, Zeilen ABSTEIGEND zu sortieren und möchte verhindern, dass leere Zellen ("") an den Anfang gestellt werden.
Folgende Lösung habe ich schon gefunden:
https://www.herber.de/forum/archiv/472to476/t475910.htm
Hat irgendjemand eine Idee?
Vielen Dank!
Hier die bekannte Lösung:
erst absteigend sortieren, dann die leere Zelle feststellen, dann nur bis Zeile dahin - 1 sortieren ...

Sub Sortalpha()
Dim zelle As Range
Rows("2:101").Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xltextasnumbers
Set zelle = Columns("A:A").Find(What:="", After:=[a1], LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not zelle Is Nothing Then
Rows("2:" & zelle.Row - 1).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xltextasnumbers
End If
End Sub

Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellen sortieren + leere Zellen ignorieren
23.01.2007 17:47:08
Markus
Ich hab noch ein zusätzliches Problem: Die Sortierreihenfolge sollte folgendermaßen ergänzt werden ablaufen:
Erst Zahlen (Absteigend) -- Dann Text (z.B. "n.r.") -- Dann Leere Zellen ("")
Ich zähl auf eure guten Ideen!
AW: Sortieren mit anderer Sortierfolge
23.01.2007 19:06:00
Erich
Hallo Markus,
probier mal
Option Explicit
Sub Sort_Zahlen_Text_Leer_abst()
Dim lngZ, Calc As XlCalculation
Const Spalt = 1   ' Spalte, nach der sortiert werden soll
Calc = Application.Calculation
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'  -------------------------------------------------------- Sortspalte generieren
lngZ = Cells(Rows.Count, Spalt).End(xlUp).Row
Columns(Spalt).Insert
Cells(1, Spalt) = "SortKey"
Range(Cells(2, Spalt), Cells(lngZ, 1)).FormulaR1C1 = "=IF(ISBLANK(RC[1])," & _
"""a""&RC[1]," & "IF(ISNUMBER(RC[1]),""c""&TEXT(RC[1],""000000000,000""),""b""&RC[1]))"
'  -------------------------------------------------------- Sortieren
Cells(1, Spalt).Sort Key1:=Cells(1, Spalt), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'  -------------------------------------------------------- Aufräumen
Columns(Spalt).Delete
Application.Calculation = Calc
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Sortieren mit anderer Sortierfolge
23.01.2007 19:48:07
Erich
Hallo Markus,
das vorige Makro hat noch ein Problem mit negativen Zahlen. Sollte hier behoben sein:
Option Explicit
Sub Sort_Zahlen_Text_Leer_abst()
Dim lngZ, Calc As XlCalculation
Const intS = 1               ' Spalte, nach der sortiert werden soll
Const strF = "000000,000"    ' Zahlenformat mit max. Anzahl Vor- und Nachkommastellen
Calc = Application.Calculation
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'  -------------------------------------------------------- Sortspalte generieren
lngZ = Cells(Rows.Count, intS).End(xlUp).Row
Columns(intS).Insert
Cells(1, intS) = "SortKey"
Range(Cells(2, intS), Cells(lngZ, 1)).FormulaLocal = "=WENN(ISTLEER(B2);" & """a""&B2;" _
& "WENN(ISTZAHL(B2);WENN(B2<0;""c"";""d"")&TEXT(B2;""" & strF & """);" & """b""&B2))"
'  -------------------------------------------------------- Sortieren
Cells(1, intS).Sort Key1:=Cells(1, intS), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'  -------------------------------------------------------- Aufräumen
Columns(intS).Delete
Application.Calculation = Calc
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Sortieren mit anderer Sortierfolge
24.01.2007 00:20:45
Erich
Hallo Markus,
und hier die dritte Version - in der zweiten war immer noch ein Fehler.
Dafür ist diese Version nun etwas variabler einsetzbar:
Option Explicit
Sub Test_Sort_var()
Call Sort_var(1, 1, True, "Abend")
Stop
Call Sort_var(1, 2, True, "Nacht")
Stop
Call Sort_var(1, 1, True, "excel")
End Sub
' 23.01.2007 Erich G.
Sub Sort_var(intSp As Integer, intO As Integer, bolGk As Boolean, strSo As String)
'         |                 |                |                 5 Zeichen für die Typen:
'         |                 |                |                    1. Zahl
'         |                 |                |                    2. Text + restl. Typen
'         |                 |                |                    3. Wahrheitswert
'         |                 |                |                    4. Fehlerwert
'         |                 |                |                    5. leere Zelle
'         |                 |                True/False für MatchCase
'         |                 1 für aufsteigend, 2 für absteigend
'         Nr. der Spalte, nach der sortiert werden soll
Dim TT(1 To 5) As String, lngZ As Long, Calc As XlCalculation
If Len(strSo) <> 5 Then Exit Sub
For lngZ = 1 To 5:    TT(lngZ) = Mid(strSo, lngZ, 1):    Next lngZ
Calc = Application.Calculation
Application.ScreenUpdating = False:   Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'  -------------------------------------------------------- Sortspalte generieren
lngZ = Cells(Rows.Count, intSp).End(xlUp).Row
Columns(intSp).Insert:   Cells(1, intSp) = "SKey1"
Range(Cells(2, intSp), Cells(lngZ, intSp)).FormulaLocal = _
"=WENN(ISTZAHL(B2);""" & TT(1) & """;" _
& "WENN(ISTLOG(B2);""" & TT(3) & """;" _
& "WENN(ISTFEHLER(B2);""" & TT(4) & """;" _
& "WENN(ISTLEER(B2);""" & TT(5) & """;""" & TT(2) & """))))"
'  -------------------------------------------------------- Sortieren nach 2 Spalten
Cells(1, intSp).Sort _
Key1:=Cells(1, intSp), Order1:=xlAscending, Key2:=Cells(1, intSp + 1), Order2:=intO, _
Header:=xlYes, OrderCustom:=1, MatchCase:=bolGk, Orientation:=xlTopToBottom
'  -------------------------------------------------------- Aufräumen
Columns(intSp).Delete
Application.Calculation = Calc
Application.EnableEvents = True:   Application.ScreenUpdating = True
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Sortieren mit anderer Sortierfolge
24.01.2007 08:35:43
Markus
Hallo,
wenn du mir jetzt noch erklären kannst, aus welchen Zeichen ich strSo zusammensetzen muss, um eine bestimmmte Sortierreihenfolge zu erhalten bin ich vollends begeistert!
Meine Sortierreihenfolge lautet: Zahlen (absteigend) dann Text dann leere Zelle
Gruß
Markus
AW: Sortieren mit anderer Sortierfolge
24.01.2007 08:48:20
Erich
Hallo Markus,
für deine Sortierung ist wichtig: 1. Zeichen kleiner als 2. Zeichen kleiner als 5. Zeichen.
Der Rest (3. Zeichen für Wahrheits-, 4. Zeichen für Fehlerwerte) ist egal, kommt nicht vor.
Mal für Spalte 4 (Die 2 steht für "absteigend"):
Call Sort_var(4, 2, False, "xyaaz")
oder
Call Sort_var(4, 2, False, "mnopq")
oder
Call Sort_var(4, 2, True, "Abend")
oder
Call Sort_var(4, 2, False, "Abend")
oder
Call Sort_var(4, 2, True, "12993")
oder
Call Sort_var(4, 2, True, "45008")
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Sortieren mit anderer Sortierfolge
24.01.2007 14:21:08
Markus
Hallo nochmal,
bei mir funktioniert das irgendwie trotzdem noch nicht.
Fehlermöglichkeit:
Die Sortierspalte bezieht sich immer auf die Spalte B?
Außerdem habe ich einige Kopfzeilen - müsste ich also auch hier den Zeilenversatz mit einbeziehen. An den anderen Stellen habe ich jeweils die Zeile 1 bzw. 2 ersetzt.
Gruß
Markus
Anzeige
AW: Sortieren mit anderer Sortierfolge
24.01.2007 15:04:18
Erich
Hallo Markus,
wie kommst du auf "Sortierspalte bezieht sich immer auf die Spalte B"?
In meinen letzten Aufrufbeispielen wird nach Spalte D (=4) sortiert.
Die 2 als 2. Parameter in diesen Beispielen steht für "absteigend".
So kannst du das Makro nicht verwenden - es kann jetzt nur mit einer Überschriftenzeile laufen.
Vielleicht hab ich bald Zeit, das auf beliebig viele Überschriftenzeilen auszubauen...
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Sortieren mit anderer Sortierfolge
24.01.2007 16:14:29
Erich
Hi Markus,
hatte zwischendurch etwas Zeit, jetzt funzt es auch mit mehreren Zeilen für die Spaltenüberschriften.
Der folgende Aufruf ist für
- 4 Zeilen für die Spaltenüberschriften
- 7 Spalten (A bis G) werden sortiert
- sortiert wird nach Spalte C (3)
- absteigend (2)
- Groß-/Kleinschr. wird berücksichtigt (bei gleichen Buchstaben erst klein, dann groß)
- Zahlen (a) vor Texten (b) vor Leerzellen (c)
- andere Typen (x) kommen nicht vor, würden ans Ende sortiert
Option Explicit
Sub Test_Sort_varU()
Call Sort_varU(4, 7, 3, 2, True, "abxxc")
End Sub
' 24.01.2007 Erich G.
Sub Sort_varU(intU As Integer, intAS As Integer, intSp As Integer, _
intO As Integer, bolGk As Boolean, strSo As String)
'  Parameter:
'  1. intU   Anzahl Zeilen für Spaltenüberschriften
'  2. intAS  Nr. der letzten mitzusortierenden Spalte
'  3. intSp  Nr. der Spalte, nach der sortiert werden soll
'  4. intO   1 für aufsteigend, 2 für absteigend
'  5. bolGk  True/False für Groß-/Kleinschreibung beachten (MatchCase)
'  6. strSo  5 Zeichen für die Datentypen
'              1. Zahl
'              2. Text (+ evtl. restliche Datentypen)
'              3. Wahrheitswert
'              4. Fehlerwert
'              5. leere Zelle
Dim TT(1 To 5) As String, lngZ As Long, Calc As XlCalculation
If Len(strSo) <> 5 Then Exit Sub
If intSp > intAS Then Exit Sub
If intAS >= Columns.Count Then Exit Sub
For lngZ = 1 To 5:    TT(lngZ) = Mid(strSo, lngZ, 1):    Next lngZ
Calc = Application.Calculation
Application.ScreenUpdating = False:   Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'  -------------------------------------------------------- Sortspalte generieren
lngZ = Cells(Rows.Count, intSp).End(xlUp).Row
Columns(intSp).Insert:   Cells(intU, intSp) = "SKey1"
Range(Cells(intU + 1, intSp), Cells(lngZ, intSp)).FormulaLocal = _
"=WENN(ISTZAHL(B2);""" & TT(1) & """;" _
& "WENN(ISTLOG(B2);""" & TT(3) & """;" _
& "WENN(ISTFEHLER(B2);""" & TT(4) & """;" _
& "WENN(ISTLEER(B2);""" & TT(5) & """;""" & TT(2) & """))))"
'  -------------------------------------------------------- Sortieren nach 2 Spalten
Range(Cells(intU, 1), Cells(lngZ, intAS + 1)).Sort _
Key1:=Cells(intU, intSp), Order1:=xlAscending, _
Key2:=Cells(intU, intSp + 1), Order2:=intO, _
Header:=xlYes, OrderCustom:=1, MatchCase:=bolGk, Orientation:=xlTopToBottom
'  -------------------------------------------------------- Aufräumen
Columns(intSp).Delete
Application.Calculation = Calc
Application.EnableEvents = True:   Application.ScreenUpdating = True
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Sortieren mit anderer Sortierfolge
24.01.2007 16:39:42
Erich
Hallo Markus,
sorry, ich hab das mit der Spalte B erst jetzt gefunden!
Also noch mal:
Der folgende Aufruf ist für
- 4 Zeilen für die Spaltenüberschriften
- 7 Spalten (A bis G) werden sortiert
- sortiert wird nach Spalte C (3)
- absteigend (2)
- Groß-/Kleinschr. wird berücksichtigt (bei gleichen Buchstaben erst klein, dann groß)
- Zahlen (a) vor Texten (b) vor Leerzellen (c)
- andere Typen (x) kommen nicht vor, würden ans Ende sortiert
Option Explicit
Sub Test_Sort_varU()
Call Sort_varU(4, 7, 3, 2, True, "abxxc")
End Sub
' 23.01.2007 Erich G.
Sub Sort_varU(intU As Integer, intAS As Integer, intSp As Integer, _
intO As Integer, bolGk As Boolean, strSo As String)
'  Parameter:
'  1. intU   Anzahl Zeilen mit Spaltenüberschriften
'  2. intAS  Nr. der letzten mitzusortierenden Spalte
'  3. intSp  Nr. der Spalte, nach der sortiert werden soll
'  4. intO   1 für aufsteigend, 2 für absteigend
'  5. bolGk  True/False für Groß-/Kleinschreibung beachten (MatchCase)
'  6. strSo  5 Zeichen für die Datentypen
'              1. Zahl
'              2. Text (+ evtl. restliche Datentypen)
'              3. Wahrheitswert
'              4. Fehlerwert
'              5. leere Zelle
Dim TT(1 To 5) As String, lngZ As Long, Calc As XlCalculation, strZ As String
If Len(strSo) <> 5 Then Exit Sub
If intSp > intAS Then Exit Sub
If intAS >= Columns.Count Then Exit Sub
For lngZ = 1 To 5:    TT(lngZ) = Mid(strSo, lngZ, 1):    Next lngZ
Calc = Application.Calculation
Application.ScreenUpdating = False:   Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'  -------------------------------------------------------- Sortspalte generieren
lngZ = Cells(Rows.Count, intSp).End(xlUp).Row
Columns(intSp).Insert:   Cells(intU, intSp) = "SKey1"
strZ = Cells(intU + 1, intSp + 1).Address(0, 0)
Range(Cells(intU + 1, intSp), Cells(lngZ, intSp)).FormulaLocal = _
"=WENN(ISTZAHL(" & strZ & ");""" & TT(1) & """;" _
& "WENN(ISTLOG(" & strZ & ");""" & TT(3) & """;" _
& "WENN(ISTFEHLER(" & strZ & ");""" & TT(4) & """;" _
& "WENN(ISTLEER(" & strZ & ");""" & TT(5) & """;""" & TT(2) & """))))"
'  -------------------------------------------------------- Sortieren nach 2 Spalten
Range(Cells(intU, 1), Cells(lngZ, intAS + 1)).Sort _
Key1:=Cells(intU, intSp), Order1:=xlAscending, _
Key2:=Cells(intU, intSp + 1), Order2:=intO, _
Header:=xlYes, OrderCustom:=1, MatchCase:=bolGk, Orientation:=xlTopToBottom
'  -------------------------------------------------------- Aufräumen
Columns(intSp).Delete
Application.Calculation = Calc
Application.EnableEvents = True:   Application.ScreenUpdating = True
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige

Infobox / Tutorial

Zellen sortieren und leere Zellen ignorieren


Schritt-für-Schritt-Anleitung

Um in Excel Zellen zu sortieren und leere Zellen zu ignorieren, gehe wie folgt vor:

  1. Datenbereich auswählen: Markiere den Bereich, den du sortieren möchtest. Achte darauf, dass dieser Bereich keine leeren Zellen enthält, da diese an den Anfang gestellt werden könnten.

  2. Sortieren absteigend: Gehe zu Daten > Sortieren. Wähle die Spalte aus, die du sortieren möchtest, und setze die Sortierreihenfolge auf Absteigend.

  3. Leere Zellen feststellen: Nutze das folgende VBA-Skript, um leere Zellen zu finden und diese am Ende der Sortierung zu platzieren:

    Sub Sortalpha()
    Dim zelle As Range
    Rows("2:101").Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xltextasnumbers
    Set zelle = Columns("A:A").Find(What:="", After:=[a1], LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
    If Not zelle Is Nothing Then
       Rows("2:" & zelle.Row - 1).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
       OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xltextasnumbers
    End If
    End Sub
  4. Makro ausführen: Führe das Makro aus, um die Zellen zu sortieren und leere Zellen am Ende anzuzeigen.


Häufige Fehler und Lösungen

  • Leere Zellen erscheinen am Anfang: Stelle sicher, dass du das Skript korrekt angepasst hast, um leere Zellen zu ignorieren.
  • Sortierung funktioniert nicht wie erwartet: Überprüfe, ob du die richtige Spalte ausgewählt hast und ob die Daten im richtigen Format vorliegen.
  • Makro läuft nicht: Achte darauf, dass du in der Entwicklertools-Sektion von Excel die Makros aktiviert hast.

Alternative Methoden

Falls du nicht mit VBA arbeiten möchtest, kannst du auch die Filterfunktion in Excel nutzen:

  1. Filter aktivieren: Wähle den Datenbereich und aktiviere den Filter unter Daten > Filter.
  2. Leere Zellen filtern: Klicke auf den Dropdown-Pfeil in der Spalte, wähle Leere Zellen aus und deaktiviere diese.
  3. Sortieren: Nun kannst du die verbleibenden Zellen sortieren, ohne dass leere Zellen angezeigt werden.

Praktische Beispiele

Ein einfaches Beispiel wäre die Sortierung von Verkaufszahlen, wobei die leeren Zellen (z.B. nicht gemeldete Verkäufe) am Ende erscheinen:

Verkaufszahlen
200
150
300

Nach der Sortierung und dem Ausführen des Makros sollte das Ergebnis so aussehen:

Verkaufszahlen
300
200
150

Tipps für Profis

  • Datenvalidierung: Stelle sicher, dass alle Daten in der Spalte die gleiche Formatierung haben, um unerwartete Sortierergebnisse zu vermeiden.
  • Fehler ignorieren: Um Excel anzuweisen, Fehler in einer ganzen Spalte zu ignorieren, verwende die Funktion AGGREGAT.
  • Sortierung nach mehreren Kriterien: Du kannst das oben genannte Makro leicht anpassen, um nach mehreren Spalten zu sortieren, indem du zusätzliche Sort-Befehle hinzufügst.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass beim Sortieren leere Zellen ignoriert werden?
Nutze das bereitgestellte VBA-Skript, um leere Zellen am Ende der Sortierung zu platzieren.

2. Was mache ich, wenn meine Daten nicht korrekt sortiert werden?
Überprüfe das Format der Zellen und stelle sicher, dass du die korrekte Spalte für die Sortierung auswählst.

3. Ist es möglich, mehrere Spalten gleichzeitig zu sortieren?
Ja, du kannst das VBA-Skript anpassen, um nach mehreren Spalten zu sortieren, indem du zusätzliche Sort-Befehle einfügst.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige