Макрос Excel VBA для сортування даних. Мені не подобається, як рекордер макросів створює додатковий код для сортування. Сортування в Excel VBA має бути простим. Один рядок коду зі стовпцем для сортування, яким шляхом (за зростанням чи за спаданням) і чи є там заголовок.
Переглянути відео
- Реєстратор макросів Excel погано справляється з сортуванням записів.
- За умови, що ваші дані можна вибрати за допомогою Ctrl + * (відомий як Поточний регіон)
- За умови, що ви не сортуєте за кольором або піктограмою або більше трьох рівнів
- Використовуйте стару школу Range (). CurrentRegion.Sort метод у Excel
Стенограма відео
Дізнайтеся Excel у MrExcel Podcast, епізод 2093: Сортування за допомогою VBA
Привіт, ласкаво просимо до мережі, я Білл Джелен. Сьогоднішнє запитання Джеймса з Хантсвіля. Джеймсе, я використовував макрофон для запису дії сортування даних. Тож припустимо, що Джеймсу потрібно було сортувати ці дані за секторами та замовниками. Отже, ви виходите сюди на вкладку Перегляд, Макроси, Запис нового макросу, SortMyReports, Комбінація клавіш Ctrl - я наберу там Shift + S і натисніть OK. Добре, тоді звідси ми робимо: Дані, Сортувати, і ми хочемо сказати, що ми хочемо Сортувати за сектором, а потім Додати рівень і Сортувати за Клієнтом, і натиснути OK. У нижньому лівому куті ми клацнемо Зупинити запис. Гаразд, так воно і є. Здається, це спрацювало, так?
Але ось проблема: завтра у вас буде більше даних або менше даних, або, знаєте, що завгодно. І цей записаний макрос просто жахливий. Давайте подивимось, я зроблю Alt + F8 і подивлюся на SortMyReport, відредагую це. Гаразд, і це все, що вони записали так SortFields.Clear, а потім вони встановили новий сорт за допомогою SortFields.Add, і вони мають жорстке кодування, і там 568 рядків, і все інше.
Перебираючи день, мені було дуже, дуже просто. Гаразд, а потім в Excel 2007 вони додали Сортувати за іконкою, Сортувати за кольором, Сортувати за кольором шрифту, можливість мати 15 рівнів сортування, і все це стало справді, дуже божевільним. Отже, я більше не використовую записаний макрос. Я просто ходжу в олдскул.
Тепер я повернусь до Excel. Ось правила для того, щоб це сортування старої школи працювало, добре. Заголовок над кожним стовпцем: цей заголовок повинен бути в одному рядку, а не в двох рядках. Якщо у вас є заголовки там, і це добре, якщо є заголовки там. Вам потрібен повністю порожній рядок між заголовками та першим заголовком. Якщо у вас є примітки з правого боку: ваша дружина дзвонить із списком продуктів: «Гей, милий, зупинись дорогою додому. Отримати молоко, яйця та горілку ”. Між вашими даними та цим має бути повністю порожній стовпець. І якщо внизу є шаблонні примітки, переконайтеся, що між останнім бітом даних та цими примітками є повністю порожній рядок.
Уся моя мета полягає в тому, що ми повинні мати можливість дійти до будь-якої однієї комірки: у верхній лівій кутовій комірці ці дані та натиснути Ctrl + *, і вона вибере дані для сортування. Тепер я збираюся натиснути Ctrl +. що веде нас до цього кута, а потім Ctrl +. приведе нас до нижнього правого кута, Ctrl +. веде нас до нижнього лівого кута. Добре, так що якщо Ctrl + * правильно відбере ваші дані, тоді все чудово. Якщо ви помістили свій список продуктів у стовпець H, і ми бачимо, що ми підійшли сюди та Ctrl + *, то ми сортуємо список продуктів як частину справи, і ваш список продуктів буде зіпсований. Або ми скасуємо: якщо цього рядка тут немає, тепер ми робимо Ctrl + *, бачимо, що ми - тепер вони будуть безглуздими, бо в них більше немає заголовків, добре?
Отже, якщо ви збираєтеся використовувати мій код, переконайтесь, що всі ці правила відповідають дійсності: не сортується мій колір, не сортується мій значок, 3 або менше рівні сортування. Скасувати, добре. Отже, ось що ми знаємо: ми знаємо, що кожен день наші дані починатимуться з A5. Якщо ми не знаємо, скільки рядків чи скільки - ну навіть скільки стовпців ми можемо мати. Я не уявляю ситуації, коли стовпці міняються місцями, але, безумовно, кількість рядків буде змінюватися. Отже, Alt + F11, ми просто почнемо з цієї верхньої лівої кутової комірки. Отже, діапазон, у моєму випадку - "A5" .CurrentRegion. Поточний регіон - це чудова властивість будівлі, в якій сказано, що ми натискаємо Ctrl + Shift + *, і все, що там є, буде відсортовано. І ми робимо. Відсортуйте, добре.
Ось у чому річ. Якщо ви хочете зробити однорівневе сортування дуже просто: Key1: =. : = і ми просто говоримо, що це буде Діапазон - О, я забуваю, що це таке. Це був Сектор, де Сектор? Сектор знаходиться у стовпці C. Тож C5 у моєму випадку, діапазон (“C5”), а потім, Order1: = xlAscending. Я натиснув там клавішу зі стрілкою вниз, а потім Tab. Гаразд, я міг би продовжувати рухатися праворуч, але я не збираюся цього робити. Я збираюся перейти до нового рядка, тому пробіл, підкреслення, щоб перейти до нового рядка, продовжує цей рядок коду, добре? І якщо у мене є сортування другого рівня: Key2: = і в цьому випадку я хочу відсортувати за замовником, який знаходиться у стовпці D, тобто D5. А потім, Order2: xlAscending. Гарний.
У мене немає сортування третього рівня, але якби ви це зробили, це був би Key3, а потім Order3. І тоді цей наступний, той, який ти повинен зробити, це Заголовок, добре? Отже, Header: = xlGuess, ось тут у вас виникають великі клопоти. І тому ми скажемо xlYes там, безумовно, як заголовок. Навіть за старих часів Макрорекордер використовував xlGuess. Я ненавиджу Excel вгадувати.
Це воно. Один рядок коду, це все, що вам потрібно зробити, і він буде працювати з більшою кількістю рядків, менше рядків. Це гарна, гарна річ. Гаразд, тож ми повернемось сюди, до Excel. Ctrl + Shift + S все ще призначено. Це зараз - Якщо ви щойно перейшли на VBA і ввели це самі, ви можете перейти до Alt + F8, знайти ім'я свого макросу, натиснути Параметри та ввести там Ctrl + Shift + S, або ми можемо навіть призначити Клавіша швидкого доступу тут, на панелі швидкого доступу. Клацніть правою кнопкою миші, Налаштуйте панель швидкого доступу, де я вибираю з наших макросів. У мене є макрос під назвою SortMyReport, натисніть кнопку Додати - я ненавиджу маленьку блок-схему там. Ми змінимо це, і я хотів би, щоб там була якась ситуація від А до Я, але, звісно, немає. Можливо, та стрілка, яка знає, хто знає, просто вибирає що-небудь.Чарівна 8-кулька, я не знаю. Я збираюся вибрати цього маленького хлопця тут, натисніть кнопку ОК, натисніть кнопку ОК. Добре, отже, тепер наші дані відсортовані за датою, я вибираю - і не має значення, що я виберу. Завжди збирається повернутися назад і відсортувати з A5, я клацну на маленького хлопця, і мої дані тепер відсортовані за секторами, в секторі, за замовником. Це чудово працює, добре?
Тож, якщо ви шанувальник Макрореєстратора, щиросердечно бажаю вам. Але код Макрорекордера сьогодні для сортування-сортування у VBA набагато простіший; щоб просто повернутися назад, просто використовуйте по суті цей, один рядок коду.
Ну, зазвичай це місце, де я намагаюся змусити вас придбати цю книгу, але сьогодні, я думаю, вам слід поглянути на цю книгу: Excel 2016 VBA та макроси від Трейсі та мене самого. Оце Так! Перевір це. Я не розумів, що існує версія іншою мовою. Ми допоможемо вам повністю піднятися на кривій навчання макросів від запису вашого першого макросу до коду, який вам потрібен.
Ну, просте підбиття підсумків на сьогоднішній день: Макрореєстратор Excel погано справляється із записом, сортуванням: за умови, що ваші дані можна вибрати за допомогою комбінації клавіш Ctrl + *, який відомий як поточний регіон, за умови, що ви не сортуєте за кольором або піктограмою або більше трьох рівнів, просто використовуйте метод old-school Range (). CurrentRegion.Sort у VBA для сортування.
Я хочу подякувати Джеймсу за те, що він надіслав це запитання. Я хочу подякувати вам за те, що завітали. Ми побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast2093.xlsm
