Zuvor habe ich in Podcast 2093 eine einfache VBA-Sortierung gezeigt, die funktioniert, wenn Sie nicht nach Farben sortieren. Heute bittet Neeta den VBA, Excel-Daten nach Farben zu sortieren.
Das Schwierigste beim Sortieren nach VBA ist, herauszufinden, welche RGB-Farbcodes Sie verwenden. In 99% der Fälle haben Sie keine Farbe durch Eingabe von RGB-Werten ausgewählt. Sie haben eine Farbe mithilfe dieser Dropdown-Liste in Excel ausgewählt.
Und während Sie Fill, More Colors, Custom verwenden können, um zu erfahren, dass die ausgewählte Farbe RGB (112,48,160) ist, ist dies ein Ärger, wenn Sie viele Farben haben.
Also - ich ziehe es vor, den Makrorecorder einzuschalten und den Makrorecorder den Code herausfinden zu lassen. Der vom Makrorecorder erzeugte Code ist niemals perfekt. Hier ist das Video, das zeigt, wie der Makrorecorder beim Sortieren nach Farben verwendet wird.
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2186: VBA Nach Farbe sortieren.
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Die heutige Frage wurde auf YouTube gesendet. Ich hatte da draußen ein Video darüber, wie man mit VBA sortiert, und sie wollten mit VBA nach Farben sortieren, was viel komplizierter ist. Ich sagte: "Warum schalten Sie nicht einfach den Makrorecorder ein und sehen, was passiert?" Und leider bringt uns der Makrorecorder, wissen Sie, uns näher, aber er bringt uns nicht ganz dorthin.
Anzeigen, Makros, Aufzeichnungsmakro, "HowToSortByColor", Makro in dieser Arbeitsmappe speichern - perfekt. OK klicken. Okay, jetzt läuft der Makrorecorder, wir kommen hierher zur Registerkarte Daten und sagen Sortieren. Wir werden ein Sortierdialogfeld verwenden und dieses erstellen, okay? Wir werden also sagen, dass wir eine Ebene hinzufügen möchten: Nach Kirsche sortieren, aber nicht nach Zellenwerten sortieren. Wir werden nach Zellenfarbe sortieren - Zellenfarbe ist dort die Füllfarbe - und wir möchten Rot oben platzieren und dann diese Ebene kopieren und Gelb an zweiter Stelle setzen. und dann fügen wir eine neue Ebene hinzu - wir gehen zu Spalte D, der Datumsspalte - Sortieren nach Zellenfarbe, zuerst rot, kopieren Sie diese Ebene, gelb und dann hierher; Und dann, hier in Elderberry, Spalte E, gibt es ein paar blaue Schriftarten, die ich nicht sehen möchte, wie das aussah.Also fügen wir das als Farbe nach Schriftart mit Blau oben hinzu. und wenn all dies ein Gleichstand für überhaupt keine Farben ist, fügen wir eine letzte Ebene nur in Spalte A hinzu - Zellwerte, Größte bis Kleinste; und klicken Sie auf OK.
Okay, jetzt ein paar Dinge - überspringen Sie diesen nächsten Schritt nicht - Ihre Datei, im Moment garantiere ich Ihnen, dass Sie als xlsx gespeichert sind. Dies ist eine großartige Zeit, um Datei, Speichern unter und als xlsm oder xlsb zu speichern. Wenn Sie dies nicht tun, geht Ihre gesamte Arbeit bis zu diesem Punkt verloren, wenn Sie diese Datei speichern. Sie löschen die Makros von allem, was in xlsx gespeichert ist. In Ordung?
Also haben wir dort aufgehört aufzunehmen und wollen uns dann unsere Makros ansehen. Sie können dies also mit Ansicht, Makros - Ansicht, Makros - tun und das Makro finden, das wir gerade aufgezeichnet haben - HowToSortByColor - und auf Bearbeiten klicken. Okay, hier ist unser Makro, und wenn ich mir das anschaue, ist das Problem, das wir haben, dass wir heute zufällig 25 Zeilen plus eine Überschrift haben. Also geht es weiter zu Zeile 26. Und sie haben fest codiert, dass sie immer zu Zeile 26 hinunterblicken werden.
Aber wenn ich darüber nachdenke, insbesondere im Vergleich zum alten VBA zum Sortieren, müssen wir nicht den gesamten Bereich angeben - nur eine Zelle in der Spalte. Überall dort, wo sie Spalte C26 haben, werde ich sie reduzieren und nur sagen: "Hey, nein, sieh dir die erste Zelle in dieser Spalte an." Also E2 und dann hier A2. In meinem Fall hatte ich also 1, 2, 3, 4, 5, 6 Sortierstufen - 6 Dinge, die ich ändern musste.
Und dann ist dies der Teil, den der Makrorecorder wirklich, wirklich schlecht bekommt, er wird immer nur nach Zeilen 26 sortieren. Also werde ich das ändern. Ich werde sagen: "Schauen Sie, beginnen Sie bei Bereich A21 und erweitern Sie ihn auf .CurrentRegion." Werfen wir einen Blick auf Excel und sehen, was das bewirkt. Wenn ich also nur eine Zelle auswählen würde - A1 oder etwas anderes - und Strg + * drücken würde, würde die aktuelle Region ausgewählt. Okay, lass es uns tun. Hier erstreckt sich Strg + * von der Mitte aus in alle Richtungen, bis es den Rand des Arbeitsblatts, den oberen Rand des Arbeitsblatts oder den rechten Rand der Daten oder den unteren Rand der Daten erreicht . Wenn Sie also A1 .CurrentRegion sagen, gehen Sie zu A1 und drücken Sie Strg + *. In Ordung? Also, hier musst du das Ding ändern. Jetzt ist alles andere im Makro in Ordnung; es'Es wird alles funktionieren. Sie haben die SortOnCellColor und SortOnFontColor und xlSortOn. Darüber muss ich mir keine Sorgen machen. Alles, was ich tun muss, ist hier nachzuschauen und zu sehen, dass sie die Region, die sie für den Bereich verwenden wollten, fest codiert haben, fest codiert haben, wie weit diese gegangen sind, und dass sie nicht fest codiert sein muss. Und mit diesem einfachen Schritt, dem Ändern dieser sechs Elemente und des siebten Elements, haben wir etwas, das funktionieren sollte.
Lassen Sie uns jetzt den Test machen. Kommen wir hier zu Excel zurück und fügen unten einige neue Zeilen hinzu. Ich werde dort nur 11s setzen, und wir werden ein paar Rotweine hinzufügen - ein Rot, ein Gelb und dann hier ein Blau. In Ordung. Wenn wir also diesen Code ausführen - diesen Code ausführen, also klicke ich hier ein und auf die Schaltfläche Ausführen - und dann zurückkomme, sollten wir sehen, dass 11 das oberste Element in Rot wurde. Es wurde dort im angezeigt Gelb, und es zeigt sich im Blues, so dass alles perfekt funktioniert. Warum ist es nach oben gegangen? Da es passiert ist, dass die letzte Sorte Spalte A ist, wird Spalte A als Tiebreaker angezeigt, wenn es ein Unentschieden gibt. Dieser Code funktioniert also.
Um zu lernen, wie man VBA schreibt, habe ich zusammen mit Tracy Syrstad eine Reihe von Büchern geschrieben, Excel VBA und MACROS. Es gibt jetzt eine Ausgabe für 2003, 2007, 2010, 2013 und 2016; Bald 2019. Also gut, suchen Sie die Version, die zu Ihrer Excel-Version passt, und dies wird Sie in die Lernkurve bringen.
Zusammenfassung: Die heutige Folge lautet: Verwendung von VBA zum Sortieren nach Farbe. Der einfachste Weg, dies zu tun, zumal Sie nicht wissen, welche RGB-Codes für jede der Farben verwendet wurden - Sie haben nur Rot gewählt, Sie wissen nicht, was der RGB-Code ist, und Sie möchten nicht nachsehen it up-- Schalten Sie den Makrorecorder mit Ansicht, Makros, Neues Makro aufnehmen ein. Wenn Sie mit dem Sortieren fertig sind, klicken Sie auf Aufzeichnung beenden - in der unteren linken Ecke - Alt + F8, um eine Liste der Makros anzuzeigen, oder Ansicht, Makros, Makro anzeigen - Registerkarte Ansicht, Makros und dann Makros anzeigen - das ist verwirrend. PSWählen Sie Ihr Makro aus und klicken Sie auf Bearbeiten. Wenn Sie C2 auf einige Bereichsnummern sehen, ändern Sie es einfach so, dass es auf Zeile 2 zeigt. Wenn dort der zu sortierende Bereich angegeben wird, wird Range ("A1"), CurrentRegion, erweitert. In Ordung.
Nun, hey, ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Im Video habe ich eine sechsstufige Sortierung eingerichtet. Das Endedialogfeld wird hier angezeigt:
An dem Tag, an dem ich das Makro aufzeichnete, hatte ich 23 Datenzeilen plus eine Überschrift. Es gab sieben Stellen im Makro, die die Anzahl der Zeilen fest codierten. Diese müssen angepasst werden.
Für jede Sortierstufe gibt es folgenden Code:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) Das ist albern, dass der Makrorecorder C2: C24 angibt. Sie müssen nur eine Zelle in der Spalte angeben. Ändern Sie daher die erste Zeile oben in:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Nehmen Sie für jede Sortierstufe eine ähnliche Änderung vor.
Gegen Ende des aufgezeichneten Makros haben Sie den aufgezeichneten Code, um die Sortierung tatsächlich durchzuführen. Es beginnt so:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithAnstatt nur A1: E24 zu sortieren, ändern Sie den Code so, dass er in A1 beginnt und sich auf die aktuelle Region erstreckt. (Die aktuelle Region erhalten Sie, wenn Sie in einer Zelle Strg + * drücken.)
.SetRange Range("A1").CurrentRegionDer endgültige Code im Video lautet:
Sub HowToSortByColor() HowToSortByColor Macro ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear ' Sort column C by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort Column C by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column D by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort column D by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column E by blue font ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("E2"), _ xlSortOnFontColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(0, 176, 240) ' Sort Column A by Values descending ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2"), _ SortOn:=xlSortOnValues, _ Order:=xlDescending, _ DataOption:=xlSortNormal ' Perform the Sort With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1").CurrentRegion .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End SubHinweis
Es ist wahrscheinlich, dass Ihre Arbeitsmappe mit einer XLSX-Erweiterung gespeichert wird. Speichern Sie unter, um zu einer XLSM- oder XLSB-Erweiterung zu wechseln. In XLSX gespeicherte Makros werden gelöscht.
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Ein Apfel am Tag hält den VBA fern."
Tom Urtis
