Раніше в Podcast 2093 я показав просте сортування VBA, яке працює, якщо ви не сортуєте за кольором. Сьогодні Neeta просить VBA сортувати дані Excel за кольором.
Найскладніше у сортуванні за VBA - з’ясувати, які кольори RGB ви використовуєте. У 99% випадків ви не вибирали колір, вводячи значення RGB. Ви вибрали колір за допомогою цього випадаючого списку в Excel.
І хоча ви можете використовувати Заливка, Більше кольорів, Спеціально, щоб дізнатись, що вибраний колір - RGB (112,48,160), це клопіт, якщо у вас багато кольорів.
Отже - я волію увімкнути макрореєстратор і дозволити макрофіксатору розібратися в коді. Код, згенерований макромагнітофоном, ніколи не є ідеальним. Ось відео, де показано, як користуватися макрофоном при сортуванні за кольором.
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2186: VBA Сортувати за кольором.
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Сьогоднішнє запитання, надіслане на YouTube. У мене було відео про те, як сортувати за допомогою VBA, і вони хотіли сортувати за кольором за допомогою VBA, що набагато складніше. Я сказав: "Чому б вам просто не увімкнути макрофон і не подивитися, що станеться?" І, на жаль, макрореєстратор, ви знаєте, він наближає нас, але не добирає нас аж туди.
Отже, View, Macros, Record Macro, "HowToSortByColor", Store Macro in This Workbook - perfect. Клацніть OK. Добре, отже, зараз працює макрофон, ми збираємось перейти сюди на вкладку Дані і сказати Сортувати. Ми будемо використовувати діалогове вікно Сортування та створимо це, добре? Отже, ми хочемо сказати, що хочемо додати рівень «Сортувати за вишнею», але не «Сортувати за значеннями комірок»; ми збираємося Сортувати за кольором комірки - Колір кольору - це колір заливки - і ми хочемо поставити червоний колір зверху, а потім скопіювати цей рівень, а жовтий поставити другим; а потім ми додамо новий рівень - ми перейдемо до стовпця D, стовпець дати - Сортувати за кольором комірки, спочатку червоним, скопіюйте цей рівень, жовтим, а потім сюди; тоді, тут, у Elderberry, стовпець E, є кілька синіх шрифтів, я не хочу бачити, як це виглядало,тож ми додамо це як колір Сортування за шрифтом із синім зверху; а потім, якщо все це зв’язок без жодних кольорів, ми додамо один заключний рівень просто у стовпці A - Значення комірок, від найбільшого до найменшого; і натисніть OK.
Добре, зараз кілька речей - не пропускайте цей наступний крок - ваш файл, прямо зараз, я гарантую, що ви зберігаєтесь як xlsx. Це чудовий час зробити Файл, Зберегти як та зберегти як xlsm або xlsb. Якщо ви цього не зробите, вся ваша робота до цього моменту буде втрачена, коли ви збережете цей файл. Вони видалять макроси з усього, що зберігається у xlsx. Добре?
Тож ми припинили там запис, а потім хочемо подивитися наші макроси. Отже, ви можете зробити це за допомогою View, Macros - View, Macros - і знайти макрос, який ми щойно записали - HowToSortByColor - і натиснути Edit. Гаразд, ось ось наш макрос, і коли я дивлюсь на це, проблема, яку ми маємо, полягає в тому, що сьогодні у нас є 25 рядків плюс заголовок. Отже, він опускається до рядка 26. І вони твердо закодували, що вони завжди будуть шукати погляд до рядків 26.
Але оскільки я думаю над цим, особливо порівняно зі старим VBA для сортування, нам не потрібно вказувати весь діапазон - лише одну клітинку в стовпці. Отже, де б вони не мали стовпець C26, я збираюся зменшити його, щоб просто сказати: "Гей, ні, подивіться на першу клітинку в цьому стовпці". Отже, E2, а потім, тут, A2. У моєму випадку у мене було 1, 2, 3, 4, 5, 6, рівні сортування - 6 речей, які потрібно змінити.
І тоді це частина, яку записувач макросів стає дуже, дуже поганим, - це те, що вони будуть весь час сортувати лише до рядків 26. Тож я збираюся це змінити. Я збираюся сказати: "Подивіться, почніть з діапазону A21 і продовжте його до .CurrentRegion." Давайте поглянемо на Excel і побачимо, що це робить. Отже, якщо я просто вибрав будь-яку одну клітинку - А1 чи щось інше - і натиснув Ctrl + *, вона вибере поточний регіон. Гаразд, давайте це зробимо. Тут, від середини, Ctrl + *, і що це робить, це те, що він простягається у всіх напрямках, поки не потрапить на край електронної таблиці, зверху електронної таблиці або в правий край даних або нижній край даних . Отже, кажучи A1 .CurrentRegion, це все одно, що перейти до A1 і натиснути Ctrl + *. Добре? Отже, тут ви повинні змінити цю річ. Тепер все інше в макросі добре; це 'всі збираються працювати. Вони отримали SortOnCellColor та SortOnFontColor та xlSortOn. Мені не треба турбуватися ні про що з цього; все, що мені потрібно зробити, це заглянути сюди і побачити, що вони жорстко закодували регіон, який збиралися використовувати для асортименту, чітко закодували, як далеко вони зайшли, і його не потрібно жорстко кодувати. І завдяки цьому простому кроку, змінивши ці шість пунктів та сьомий пункт, ми маємо щось, що мало б працювати.
Тепер давайте проведемо тест. Повернемося сюди до Excel, і ми додамо кілька нових рядків унизу. Я просто поставлю туди 11, і ми додамо пару червоних - червоний, жовтий, а потім сюди синій. Гаразд Отже, якщо ми запустимо цей код - запустимо цей код, я натискаю тут і натискаю кнопку Виконати, - а потім повертаюся, ми побачимо, що цей 11 став головним елементом червоного кольору, він з’явився там жовті, і це проявляється в блюзі, так що все працює ідеально. Чому воно пішло на вершину? Оскільки траплялося, що останнім сортуванням є стовпець A, і тому, коли є нічия, вона виглядає в стовпці A як переривник. Отже, цей код працює.
Щоб навчитися писати VBA, я разом із Трейсі Сирстад написав серію книг Excel VBA та MACROS. Зараз існує видання за 2003, 2007, 2010, 2013 та 2016 роки; незабаром 2019. Добре, знайдіть версію, яка відповідає вашій версії Excel, і це підніме вас на шляху до навчання.
Підсумок: Сьогоднішній епізод - Як використовувати VBA для сортування за кольором. Найпростіший спосіб зробити це, тим більше, що ви не знаєте, які коди RGB використовувались для кожного з кольорів - ви просто вибрали червоний, не знаєте, що таке RGB-код, і не хочете шукати it up - увімкніть запис макросів за допомогою View, Macros, Record New Macro. Закінчивши сортування, натисніть Зупинити запис - це в нижньому лівому куті - Alt + F8, щоб побачити список макросів, або Перегляд, макроси, макрос перегляду - вкладка Вигляд, макроси та потім Переглянути макроси - це бентежить. PSВиберіть свій макрос і натисніть Редагувати, і щоразу, коли ви бачите C2 до деяких номерів діапазону, просто змініть його на пункт 2. Рядок 2. А потім, де вони вкажуть діапазон для сортування, Range ("A1"), CurrentRegion, розшириться. Гаразд
Ну, привіт, я хочу подякувати вам за те, що завітали, до наступного разу побачимось ще на одній трансляції від.
У відео я встановив шестирівневе сортування. Діалогове вікно закінчення показано тут:
У той день, коли мені довелося записати макрос, у мене було 23 рядки даних плюс заголовок. У макросі було сім місць, які жорстко закодували кількість рядків. Вони повинні бути скориговані.
Для кожного рівня сортування є такий код:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) Це нерозумно, що макрофон записує C2: C24. Вам потрібно вказати лише одну клітинку в стовпці, тому змініть перший рядок вище на:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Внесіть подібну зміну для кожного з рівнів сортування.
Близько кінця записаного макросу у вас є записаний код, який насправді виконує сортування. Все починається так:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithЗамість того, щоб сортувати лише A1: E24, змініть код, щоб він починався з A1 і поширювався на поточну область. (Поточна область - це те, що ви отримуєте, якщо натиснути Ctrl + * з клітинки).
.SetRange Range("A1").CurrentRegionОстаточний код, показаний у відео:
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 SubПримітка
Ймовірно, що ваша книга зберігається з розширенням XLSX. Зробіть Зберегти як, щоб змінити розширення XLSM або XLSB. Будь-які макроси, збережені в XLSX, видаляються.
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Яблуко на день тримає VBA далеко".
Том Уртіс
