Макрос Excel удаляет пустые строки в Excel

Обновлено: 21.11.2024

Это руководство научит вас нескольким простым приемам безопасного удаления нескольких пустых строк в Excel без потери информации.

Пустые строки в Excel — это проблема, с которой мы все время от времени сталкиваемся, особенно при объединении данных из разных источников или импорте информации откуда-то еще. Пустые строки могут нанести большой ущерб вашим рабочим листам на разных уровнях, а их удаление вручную может занять много времени и привести к ошибкам. В этой статье вы узнаете о нескольких простых и надежных методах удаления пробелов на листах.

Как НЕ удалять пустые строки в Excel

Существует несколько различных способов удаления пустых строк в Excel, но на удивление многие интернет-ресурсы придерживаются наиболее опасного способа, а именно Найти и выделить > Перейти к специальному > Пробелы.

Что не так с этой техникой? Он выбирает все пробелы в диапазоне, и, следовательно, вы в конечном итоге удалите все строки, содержащие хотя бы одну пустую ячейку.

На изображении ниже показана исходная таблица слева и результирующая таблица справа. И в результирующей таблице все незавершенные строки исчезли, даже строка 10, в которой отсутствовала только дата в столбце D:

Вывод: если вы не хотите испортить данные, никогда не удаляйте пустые строки, выбирая пустые ячейки. Вместо этого используйте один из более продуманных подходов, обсуждаемых ниже.

Как удалить пустые строки в Excel с помощью VBA

Excel VBA может многое исправить, в том числе несколько пустых строк. Лучшее в этом подходе то, что он не требует навыков программирования. Просто возьмите один из приведенных ниже кодов и запустите его в своем Excel (инструкции здесь).

Макрос 1. Удалить пустые строки в выбранном диапазоне

Этот код VBA автоматически удаляет все пустые строки в выбранном диапазоне, не показывая пользователю никаких сообщений или диалоговых окон.

В отличие от предыдущего метода, макрос удаляет строку, если вся строка пуста. Он использует функцию рабочего листа CountA для получения количества непустых ячеек в каждой строке, а затем удаляет строки с нулевым количеством.

Чтобы дать пользователю возможность выбрать целевой диапазон после запуска макроса, используйте следующий код:

После запуска макрос показывает следующее поле ввода, вы выбираете целевой диапазон и нажимаете OK:

Через мгновение все пустые строки в выбранном диапазоне будут удалены, а оставшиеся сместятся вверх:

Макрос 2. Удалить все пустые строки в Excel

Чтобы удалить все пустые строки на активном листе, определите последнюю строку используемого диапазона (т.е. строку, содержащую последнюю ячейку с данными), а затем перейдите вверх, удалив строки, для которых CountA возвращает ноль:

Макрос 3. Удалить строку, если ячейка пуста

С помощью этого макроса вы можете удалить всю строку, если ячейка в указанном столбце пуста.

Следующий код проверяет столбец A на наличие пробелов. Чтобы удалить строки на основе другого столбца, замените "A" соответствующей буквой.

На самом деле макрос использует функцию Перейти к специальному > Пробелы, и вы можете добиться того же результата, выполнив эти шаги вручную.

Примечание. Макрос удаляет пустые строки во всем листе, поэтому будьте очень осторожны при его использовании. В качестве меры предосторожности может быть целесообразно создать резервную копию рабочего листа перед запуском этого макроса.

Как удалить пустые строки в Excel с помощью VBA

Чтобы удалить пустые строки в Excel с помощью макроса, вы можете либо вставить код VBA в свою книгу, либо запустить макрос из нашего образца книги.

Добавить макрос в книгу

Чтобы вставить макрос в книгу, выполните следующие действия:

  1. Откройте лист, на котором вы хотите удалить пустые строки.
  2. Нажмите клавиши ALT + F11, чтобы открыть редактор Visual Basic.
  3. На левой панели щелкните правой кнопкой мыши Эта книга и выберите Вставить >Модуль.
  4. Вставьте код в окно кода.
  5. Нажмите F5, чтобы запустить макрос.

Подробные пошаговые инструкции см. в разделе Как вставить и использовать VBA в Excel.

Запустите макрос из нашего примера книги

DeleteBlankRows – удаляет пустые строки в текущем выбранном диапазоне.

RemoveBlankLines — удаляет пустые строки и сдвигает вверх диапазон, выбранный вами после запуска макроса.

DeleteAllEmptyRows – удаляет все пустые строки на активном листе.

DeleteRowIfCellBlank – удаляет строку, если ячейка в определенном столбце пуста.

Чтобы запустить макрос в Excel, сделайте следующее:

  1. Откройте загруженную книгу и включите макросы, если будет предложено.
  2. Откройте свою книгу и перейдите к интересующему листу.
  3. На листе нажмите клавиши ALT + F8, выберите макрос и нажмите "Выполнить".

Формула для удаления пустых строк в Excel

Если вы хотите увидеть, что вы удаляете, используйте следующую формулу для определения пустых строк:

=IF(COUNTA(A2:D2)=0, "Пусто", "Не пусто")

Где A2 — первая, а D2 — последняя использованная ячейка первой строки данных.

Введите эту формулу в E2 или любой другой пустой столбец строки 2 и перетащите маркер заполнения, чтобы скопировать формулу вниз.

В результате у вас будет "Пусто" в пустых строках и "Не пусто" в строках, содержащих хотя бы одну ячейку с данными:

Логика формулы очевидна: вы подсчитываете непустые ячейки с помощью функции COUNTA и используете оператор IF, чтобы вернуть "пусто" для нулевого подсчета и "не пусто" в противном случае.

На самом деле можно прекрасно обойтись и без ЕСЛИ:

В этом случае формула вернет TRUE для пустых строк и FALSE для непустых строк.

Установив формулу, выполните следующие действия, чтобы удалить пустые строки:

  1. Выберите любую ячейку в строке заголовка и нажмите «Сортировка и фильтр» > «Фильтр» на вкладке Главная в разделе Форматы. ячейки заголовка.
  2. Нажмите стрелку в заголовке столбца формулы, снимите флажок ("Выбрать все"), выберите "Пусто" и нажмите ОК:
  3. Выберите все отфильтрованные строки. Для этого щелкните первую ячейку первой отфильтрованной строки и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки последней отфильтрованной строки.
  4. Нажмите правой кнопкой мыши на выделенном фрагменте, выберите "Удалить строку" в контекстном меню, а затем подтвердите, что вы действительно хотите удалить строки целиком:
  5. Удалите фильтр, нажав Ctrl + Shift + L. Или перейдите на вкладку Главная >Сортировка и фильтр >Фильтр.
  6. Удалите столбец с формулой, так как он вам больше не нужен.

    7. Вот оно! В результате имеем чистую таблицу без пустых строк, но с сохранением всей информации:

    Совет. Вместо удаления пустых строк вы можете скопировать непустые строки в другое место. Для этого отфильтруйте «Непустые» строки, выделите их и нажмите Ctrl + C, чтобы скопировать. Затем переключитесь на другой лист, выберите верхнюю левую ячейку целевого диапазона и нажмите Ctrl + V для вставки.

    Как удалить пустые строки в Excel с помощью Power Query

    В Excel 2016 и Excel 2019 есть еще один способ удалить пустые строки — с помощью функции Power Query. В Excel 2010 и Excel 2013 его можно загрузить как надстройку.

    Важное примечание! Этот метод работает со следующей оговоркой: Power Query преобразует исходные данные в таблицу Excel и изменяет форматирование, например цвет заливки, границы и некоторые числовые форматы. Если для вас важно форматирование ваших исходных данных, вам лучше выбрать другой способ удаления пустых строк в Excel.

    В результате этих манипуляций я получил следующую таблицу без пустых строк, но с парой неприятных изменений - формат валюты потерян и даты отображаются в формате по умолчанию вместо пользовательского:

    Как удалить строки, если ячейка в определенном столбце пуста

    В начале этого урока мы предупреждали вас об удалении пустых строк путем выделения пробелов. Однако этот метод удобен, если вы хотите удалить строки на основе пробелов в определенном столбце.

    В качестве примера давайте удалим все строки, в которых ячейка в столбце A пуста:

    Того же результата можно добиться, отфильтровав пробелы в ключевом столбце.

    Как удалить лишние строки под данными

    Иногда строки, которые выглядят совершенно пустыми, на самом деле могут содержать некоторые форматы или непечатаемые символы. Чтобы проверить, действительно ли последняя ячейка с данными является последней используемой ячейкой на вашем листе, нажмите Ctrl + End . Если это привело вас к визуально пустой строке под вашими данными, с точки зрения Excel, эта строка не пуста. Чтобы удалить такие строки, сделайте следующее:

    1. Нажмите на заголовок первой пустой строки под вашими данными, чтобы выбрать ее.
    2. Нажмите Ctrl + Shift + End . При этом будут выбраны все строки, содержащие что-либо, включая форматы, пробелы и непечатаемые символы.
    3. Нажмите правой кнопкой мыши на выделенном фрагменте и выберите "Удалить..." >Вся строка.

    Если у вас относительно небольшой набор данных, вы можете избавиться от всех пустых строк под вашими данными, например для облегчения прокрутки. Хотя в Excel нет возможности удалить неиспользуемые строки, нет ничего, что мешало бы вам их скрыть. Вот как:

    1. Выберите строку под последней строкой с данными, щелкнув ее заголовок.
    2. Нажмите Ctrl + Shift + Стрелка вниз, чтобы расширить выделение до последней строки на листе.
    3. Нажмите Ctrl + 9, чтобы скрыть выбранные строки. Или щелкните правой кнопкой мыши выделенный фрагмент и выберите "Скрыть".

    Чтобы отобразить строки, нажмите Ctrl + A, чтобы выделить весь лист, а затем нажмите Ctrl + Shift + 9, чтобы снова сделать все строки видимыми.

    Подобным образом вы можете скрыть неиспользуемые пустые столбцы справа от ваших данных. Подробные инструкции см. в разделе Скрытие неиспользуемых строк в Excel, чтобы была видна только рабочая область.

    Самый быстрый способ удалить пустые строки в Excel

    При чтении предыдущих примеров вам не показалось, что мы используем кувалду, чтобы расколоть орех? Здесь, в Ablebits, мы предпочитаем не усложнять вещи больше, чем они должны быть. Итак, мы пошли дальше и создали маршрут в два клика для удаления пустых строк в Excel.

    Добавив на ленту Ultimate Suite, вы можете удалить все пустые строки на листе следующим образом:

    Как показано на снимке экрана ниже, мы удалили только абсолютно пустые строки, в которых нет ни одной ячейки с данными:

    Чтобы узнать о других замечательных функциях Ultimate Suite for Excel, загрузите пробную версию.

    Я хочу удалить пустые строки, которые создает мое предложение ERP. Я пытаюсь просмотреть документ (A1:Z50) и для каждой строки, в которой нет данных в ячейках (A1-B1. Z1 = пусто, A5-B5. Z5 = пусто), я хочу их удалить.< /p>

    Я нашел это, но не могу настроить его для себя.

    Серг, кажется, я неправильно объяснил свою проблему. Мне нужно проверить всю строку (A1-Z1), чтобы увидеть, пуста она или нет, и это до A50-Z50.

    Да, столбцы с A1 по Z1 для меня представляют собой целую строку. Если ни в одной из этих ячеек (от A1 до Z1) нет элементов, строка пуста и может быть удалена.

    9 ответов 9

    Было не так уж плохо, просто слишком долго для моих нужд. Тем не менее, кто-то еще, у кого может быть такая же проблема, поможет с вашим примером;)

    ЕСЛИ вы хотите удалить всю строку, используйте этот код

    Спасибо за ваши усилия, не уверен, что это работает, но это немного длиннее, чем фрагмент, который я принял.

    @Tom: Это длиннее, потому что я использовал правильный способ кодирования со всей обработкой ошибок. В любом случае оставь это ;)

    Я знаю, что опаздываю на вечеринку, но вот код, который я написал/использую для выполнения этой работы.

    для тех, кто хочет удалить "пустые" и "пустые" строки (Ctrl + Shift + End вглубь вашего листа). Вот мой код. Он найдет последнюю «настоящую» строку на каждом листе и удалит оставшиеся пустые строки.

    Откройте VBA ( ALT + F11 ), Вставьте -> Модуль, Скопируйте мой код и запустите его с помощью F5. И вуаля :D

    У меня есть еще один для случая, когда вы хотите удалить только полностью пустые строки, но не отдельные пустые ячейки. Он также работает вне Excel, например.при доступе к Excel с помощью Access-VBA или VB6.

    работает, просто я рекомендую использовать Sub DeleteEmptyRows(Sheet) вместо DeleteEmptyRows(Sheet As Excel.Worksheet) . Я использовал DeleteEmptyRows(Workbooks.Open(path).Sheets("sheet1"))

    Хороший совет. Фактически, я взял это из своего класса Excel, поэтому Sheet обычно является внутренней локальной переменной класса, а заголовок Subs будет просто Sub DeleteEmptyRows() . Чтобы адаптировать его здесь, я добавил параметр, чтобы он работал как отдельная функция. Это, возможно, противоречит тому факту, что у нас есть в Excel Worksheet-Objects, а также Sheet-Objects, которые не одинаковы в каждом случае, несмотря на то, что они описывают одну и ту же сущность. Не стесняйтесь изменять его в соответствии с вашими потребностями.

    Чтобы сделать ответ Alex K немного более динамичным, вы можете использовать следующий код:

    Это было получено с этого веб-сайта, а затем немного адаптировано, чтобы позволить пользователю выбирать, с какого листа он хочет удалить пустые строки.

    Чтобы функция возобновления при ошибке работала, вы должны объявить значения рабочей книги и рабочего листа как таковые

    У меня была та же проблема, и это устранило все пустые строки без необходимости реализации цикла For.

    Это отлично сработало для меня (вы можете настроить lastrow и lastcol по мере необходимости):

    Вот самый быстрый способ удалить все пустые строки (на основе одного столбца)

    Не тот ответ, который вы ищете? Просмотрите другие вопросы с тегом vba excel или задайте свой вопрос.

    Связано

    Связанные

    Горячие вопросы о сети

    Чтобы подписаться на этот RSS-канал, скопируйте и вставьте этот URL-адрес в программу для чтения RSS.

    дизайн сайта / логотип © 2022 Stack Exchange Inc; вклады пользователей под лицензией cc by-sa. версия 2022.3.22.41738

    Сегодня я покажу вам, как с помощью VBA можно удалить пустые строки в Excel из набора данных.

    Загрузить практическую рабочую тетрадь

    Удалить пустые строки в Excel с помощью VBA

    Здесь у нас есть набор данных с именами некоторых учащихся и их оценками по английскому языку на ежегодном экзамене в школе под названием Sunflower Kindergarten.

    Но по ошибке в наборе данных было создано несколько пустых строк, которые мы хотим удалить.

    Сегодня я покажу вам, как удалить пустые строки из этого набора данных с помощью макроса (код VBA).

    Шаг 1. Откройте окно VBA, чтобы удалить пустые строки в Excel

    ➤ Нажмите клавиши ALT+F11 на клавиатуре. Откроется окно VBA.

    Шаг 2. Вставьте модуль VBA для удаления пустых строк в Excel

    ➤ Перейдите на вкладку "Вставка" в окне VBA.

    ➤ Из доступных вариантов выберите Модуль.

    Шаг 3. Внедрение кода VBA для удаления пустых строк

    ➤ Откроется новое окно модуля под названием «Модуль 1».

    ➤ Вставьте следующий код VBA в модуль.

    Код:

    Примечания:

    • Этот код создает макрос с именем Delete_Rows.
    • В 12-й строке кода Rows(j + 3).EntireRow.Delete , 3 – это количество строк до левого перед первой строкой вашего набора данных.
    • Вы меняете его в соответствии с вашим набором данных.

    Шаг 4. Сохраните книгу как функцию с поддержкой макросов, чтобы удалить пустые строки в Excel

    ➤ Сохраните книгу как книгу Excel с поддержкой макросов.

    Шаг 5. Выберите данные, из которых вы хотите удалить пустые строки в Excel

    ➤ Вернитесь на рабочий лист, выберите набор данных (без заголовков столбцов).

    Шаг 6. Запустите макрос VBA для удаления строк с пустыми ячейками в Excel

    ➤ Нажмите клавиши ALT+F8 на клавиатуре.

    ➤ Откроется диалоговое окно «Макрос». Выберите Delete_Rows и нажмите «Выполнить».

    Шаг 7. Окончательный вывод для удаления пустых строк в Excel

    ➤ Вы обнаружите, что пустые строки удаляются автоматически.

    Заключение

    С помощью этих шагов вы можете удалить пустые строки из набора данных в Excel. У вас есть вопросы? Не стесняйтесь спрашивать нас.

    Похожие материалы

    • Как удалить определенные строки в Excel (8 быстрых способов)
    • Как удалить каждую вторую строку в Excel (4 метода)
    • Удалить строки в Excel: 7 способов
    • Как удалить выбранные строки в Excel (8 подходов)
    • Как удалить несколько строк в Excel с условием (3 способа)
    • Удалить несколько строк в Excel с помощью VBA (4 способа)

    Рифат Хассан

    Здравствуйте! Добро пожаловать в мой профиль. Здесь я буду публиковать статьи, связанные с Microsoft Excel. Я страстный инженер-электрик со степенью бакалавра в области электротехники и электронной техники Бангладешского инженерно-технологического университета. Помимо академических занятий, я всегда люблю идти в ногу с революцией в технологиях, к которой мир стремится изо дня в день. Я прилежна, ориентирована на карьеру и готова дорожить знаниями на протяжении всей жизни.

    Пустые строки или пустые ячейки в листах данных могут сильно раздражать. Хотя преднамеренная вставка пустых строк иногда может облегчить чтение и понимание отчетов.

    Но если вы планируете импортировать электронную таблицу в какое-либо другое приложение (например, Microsoft Access), то эти надоедливые пустые строки сделают вашу жизнь мучительной.

    В этом уроке я расскажу вам, как удалить эти пустые строки в Excel.

    Оглавление

    Способ 1: удалить пустые строки в Excel

    В этом методе мы будем использовать функцию Excel GoTo Special для поиска пробелов. Ниже приведена пошаговая процедура для этого:

    • Прежде всего откройте лист Excel, где вы хотите удалить пустые строки.

    • Затем перейдите на «Главная» > «Найти и выбрать» > «Перейти к специальному»

    • Теперь вы увидите диалоговое окно «Перейти к специальному». Здесь установите переключатель «Пробелы» и нажмите «ОК».

    • При этом будут выбраны все пустые ячейки одновременно. Теперь вам просто нужно удалить их.

    • Чтобы сделать это, не щелкая где-либо еще на листе (в противном случае ваш выбор растворится в воздухе), просто выберите «Главная» > «Удалить» > «Удалить строки листа».

    Примечание. Есть одна вещь, с которой вам следует быть осторожным. Если в каком-либо столбце есть отсутствующие значения, они также будут удалены, поэтому данные будут смещены и могут привести к несогласованности.

    Чтобы выполнить описанные выше шаги с помощью клавиатуры, просто нажмите следующие клавиши после выбора данных:

    Способ 2. Удаление пустых строк с помощью функции поиска Excel

    В этом методе мы воспользуемся функцией поиска Excel. Ниже приведено пошаговое руководство для этого:

    • Сначала выберите набор данных и нажмите клавиши Ctrl + F, чтобы открыть диалоговое окно «Найти и заменить».

    • Затем нажмите кнопку "Параметры" и выберите "Значения" в раскрывающемся списке "Искать". Оставьте текстовое поле «Найти что» пустым и нажмите «Найти все».
    • После этого в диалоговом окне «Найти и заменить» отобразятся все пустые ячейки. Щелкните любую запись и нажмите Ctrl + A, чтобы выделить все пустые ячейки.

    • Теперь закройте диалоговое окно «Найти и заменить» и, не нажимая больше ничего, перейдите к «Главная» > «Удалить» > «Удалить строки».

    Способ 3. Удаление пробелов с помощью функции фильтра Excel

    В этом методе мы собираемся использовать функцию автоматического фильтра Excel для удаления пустых строк. Чтобы использовать этот метод, выполните следующие действия:

    • Прежде всего выберите диапазон, из которого нужно удалить незаполненные строки.

    • Перейдите к «Главная» > «Сортировка и фильтр» > «Фильтр» или нажмите клавиши Shift + Ctrl + L, чтобы применить фильтр.

    • После этого выберите столбец, щелкните раскрывающийся список фильтров, снимите флажки со всех значений, кроме «Пробелы», и нажмите «ОК».

    • Теперь, выделив все незаполненные строки, перейдите к «Главная» > «Удалить» > «Удалить строки». Наконец, удалите фильтр, и вы увидите, что незаполненные строки удалены.

    Способ 4. Удаление пустых строк с помощью макроса

    Если вы хотите удалить пустые строки с помощью макроса, вы можете использовать следующий код. Этот код внутри использует тот же механизм, что и в методе 1.

    Итак, ниже приведен код:

    Обратите внимание, что перед запуском этого макроса вам необходимо выбрать диапазон, в котором вы пытаетесь удалить пробелы.

    Примечание. Для запуска этого кода может потребоваться включить макросы Excel. В этой статье объясняется, как это сделать.

    Итак, вот несколько простых способов удалить пустые строки в Excel. Поделитесь любыми другими способами сделать то же самое.

    Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

    Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

    Читайте также: