Ранее в Podcast 2093 я показывал простую сортировку VBA, которая работает, если вы не выполняете сортировку по цвету. Сегодня Neeta просит VBA отсортировать данные Excel по цвету.
Самое сложное в сортировке с помощью VBA - выяснить, какие цветовые коды RGB вы используете. В 99% случаев вы не выбирали цвет, вводя значения RGB. Вы выбрали цвет в раскрывающемся списке Excel.
И хотя вы можете использовать «Заливка», «Больше цветов», «Пользовательский», чтобы узнать, что выбранный цвет - RGB (112,48,160), это будет проблемой, если у вас много цветов.
Итак - я предпочитаю включать регистратор макросов и позволять регистратору макросов определять код. Код, созданный регистратором макросов, никогда не бывает идеальным. Вот видео, показывающее, как использовать макрорекордер при сортировке по цвету.
Стенограмма видео
Изучите Excel из подкаста, эпизод 2186: VBA Sort by Color.
Привет, добро пожаловать обратно в сеть, я Билл Джелен. Сегодняшний вопрос отправлен на YouTube. У меня было видео о том, как сортировать с помощью VBA, и они хотели отсортировать по цвету с помощью VBA, что намного сложнее. Я сказал: «Почему бы тебе просто не включить макрорекордер и посмотреть, что произойдет?» И, к сожалению, макрорекордер, вы знаете, приближает нас, но не до конца.
Итак, просмотр, макросы, запись макроса, «HowToSortByColor», сохранение макроса в этой книге - идеально. Щелкните ОК. Хорошо, теперь программа записи макросов запущена, мы перейдем на вкладку «Данные» и скажем «Сортировка». Мы собираемся использовать диалоговое окно «Сортировка», и мы его построим, хорошо? Итак, мы собираемся сказать, что хотим добавить уровень «Сортировка по вишне», но не «Сортировка по значениям ячеек»; мы собираемся выполнить сортировку по цвету ячейки - цвет ячейки - это цвет заливки - и мы хотим поставить красный цвет сверху, а затем скопировать этот уровень и поставить второй желтый; а затем мы добавим новый уровень - мы перейдем к столбцу D, столбцу даты - Сортировка по цвету ячейки, сначала красный, скопируем этот уровень, желтый, а затем сюда; а затем здесь, в Elderberry, в столбце E, есть несколько синих шрифтов, я не хочу видеть, как это выглядело,поэтому мы добавим это как Сортировка по цвету шрифта с синим сверху; а затем, если все это связано с отсутствием цветов вообще, мы добавим один последний уровень только в столбец A - Значения ячеек, от наибольшего к наименьшему; и нажмите ОК.
Хорошо, а теперь пара вещей - не пропускайте этот следующий шаг - прямо сейчас ваш файл, я гарантирую, вы сохранены как 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 и посмотрим, что он делает. Итак, если бы я просто выбрал любую одну ячейку - A1 или что-то еще - и нажал 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, чтобы увидеть список макросов, или Просмотр, Макросы, Просмотр макроса - вкладка Просмотр, Макросы и затем Просмотреть макросы - это сбивает с толку. PВыберите свой макрос и нажмите «Изменить», и в любое время, когда вы увидите C2 для некоторых номеров диапазона, просто измените его так, чтобы он указывал на строку 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 по поводу Excel. Сегодняшняя мысль задуматься:
«Яблоко в день избавляет от VBA».
Том Уртис
