Как удалить неиспользуемые строки ниже рабочей области в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Excel works!

Вы работаете с большим списком номенклатуры, что-то нужно удалить, что-то добавить (для удаления не забывайте пользоваться горячими клавишами ctrl+ ‘-‘ ). Но что делать если вы удалили не строки, а просто данные в этих строках? И осталась таблица с «пропусками». Как удалить пустые строки в Excel сразу? Как всегда есть сразу несколько способов.

Нам нужно удалить строки 7 и 3, но как правило их гораздо больше. Рассмотрим не «ручные» методы удаления.

Как удалить пустые строки в Excel? Способ 1. Фильтр

Применяйте для нашей таблицы Автофильтр . Главная — Редактирование — Сортировка и фильтр. А затем отбирайте только пустые строки по нужному столбцу

Удалить строки2

Останутся только пустые строки. Выделяйте и удаляйте.

Способ 2. Сортировка

Способ еще проще: сортируем все ячейки по любому столбцу — пустые строки ушли.

Удалить строки3

Не забываем, что сортировать можно не только по строкам, но и по столбцам, т.е. можно удалить и пустые столбцы. Подробнее читайте здесь .

Способ 3. Выделение группы ячеек

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

Выделяем таблицу. Переходим Главная — Редактирование — Найти и выделить — Выделение группы ячеек. В открывшемся окне выбираем Пустые ячейки.

Удалить строки 4

Выделятся только пустые ячейки — правой кнопкой мыши, удалить строки или на ленте Главная — Ячейки — Удалить.

Внимание! Ячейки, содержащие формулы, пустыми считаться не будут.

Способ 4. Макрос

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

Sub RowDel() LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.Rows(r).Columns(1).Value = "" Then Rows(r).Delete Next r End Sub

Т.е. мы находим последнюю строку и циклом удаляем все строки (от последней к первой), у которых в первом столбце значение равно «», т.е. пусто. Ну и для быстроты не показываем эти вычисления (ScreenUpdating ).

Часто бывает нужно не удалить строку, а удалить ее содержимое — замените в коде

Rows(r).Delete на

Rows(r).Clear

Видимо, когда-то давно занял этот макрос на ПланетаExcel. Им отдельное спасибо за все, что они делают!

Как удалить все пустые строки ниже рабочей области?

Читайте подробно в этой статье . А если вкратце то:

Найдите последнюю ячейку в таблице, выделите первую пустую ячейку после нее (а лучше первую пустую строку), нажмите Ctrl+Shift+End. Так мы выделим все ячейки ниже рабочей таблицы. Теперь удалим их.

Пишите комментарии, если удалить пустые строки не получается.

покупка

Вам когда-нибудь было скучно удалять бесконечные пустые строки в Excel? Поиск и удаление их по одному должно быть трудным. Здесь я расскажу о некоторых методах быстрого решения этой проблемы.

Удалите бесконечные пустые строки в выделении / листе / книге с помощью Kutools for Excel

Показать только выделение и скрыть другой диапазон с помощью Kutools for Excel

Удалите бесконечные пустые строки с помощью Go Special и Delete

1. Выберите весь лист, нажав Alt + A клавиши, следующее нажатие Ctrl + G ключи для включения Перейти к диалога, а затем нажмите Особый. Смотрите скриншот:

2. в Перейти к специальному диалог, проверьте Пробелы вариант. Смотрите скриншот:

3. Нажмите OK, теперь все пустые ячейки в пустых строках выбраны.

4. Щелкните правой кнопкой мыши одну из этих выбранных пустых ячеек и выберите Удалить из контекстного меню. Смотрите скриншот:

5. в Удалить диалог, проверьте Весь строка и щелкните OK.

Теперь пустые строки удалены.

документ удалить бесконечные пустые строки 6

документ удалить бесконечные пустые строки 7

Удалите бесконечные пустые строки в выделении / листе / книге с помощью Kutools for Excel

Если вы хотите удалить все пустые строки во всей книге, насколько хлопотно вы удаляете строки по одному листу за другим. Однако, если у вас есть Kutools for ExcelАвтора Удалить пустые строки полезность, это кусок пирога.

После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)

Включите лист и нажмите Кутулс > Удалить > Удалить пустые строки, и выберите нужный вариант в подменю.

Показать только выделение и скрыть другой диапазон с помощью Kutools for Excel

Если вы хотите показать только выбранный диапазон данных и скрыть другой пустой диапазон, вы можете применить Kutools for ExcelАвтора Установить область прокрутки утилита.

После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)

Выберите область, которую вы хотите только отображать, и нажмите Кутулс > Показать спрятать > Установить область прокрутки. Смотрите скриншот:

документ удалить бесконечные пустые строки 9

документ удалить бесконечные пустые строки 10

Удаление пустых строк и столбцов в данных

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

Способ 1. Поиск пустых ячеек

Исходные данные

Это, может, и не самый удобный, но точно самый простой способ вполне достойный упоминания. Предположим, что мы имеем дело вот с такой таблицей, содержащей внутри множество пустых строк и столбцов (для наглядности выделены цветом):

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

Выделяем диапазон с городами (B2:B26)
Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная — Найти и выделить — Выделить группу ячеек (Home — Find&Select — Go to special) .
В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
Теперь выбираем на вкладке Главная команду Удалить — Удалить строки с листа (Delete — Delete rows) или жмём сочетание клавиш Ctrl + минус — и наша задача решена.

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

Способ 2. Поиск незаполненных строк

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

Взгляните, например, на следующую таблицу — как раз такой случай:

Исходные данные с пустыми ячейками

Здесь подход будет чуть похитрее:

СЧЁТЗ (COUNTA)

Считаем количество заполненных ячеек

К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.

Способ 3. Макрос удаления всех пустых строк и столбцов на листе

Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt + F11 или выберите на вкладке Разработчик — Visual Basic (Developer — Visual Basic Editor) . Если вкладки Разработчик не видно, то можно включить ее через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) .

В открывшемся окне редактора Visual Basic выберите команду меню Insert — Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:

Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns(r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub

Закройте редактор и вернитесь в Excel.

Теперь нажмите сочетание Alt + F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) — все пустые строки и столбцы на листе будут мгновенно удалены.

Способ 4. Запрос Power Query

Ещё один способ решить нашу задачу и весьма частый сценарий — это удаление пустых строк и столбцов в Power Query.

Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую «умную» сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:

Присваиваем имя диапазону данных

Теперь используем команду Данные — Получить данные — Из таблицы/диапазона (Data — Get Data — From table/range) и грузим всё в Power Query:

Загруженные в Power Query данные

Дальше всё просто:

Удаляем пустые строки командой Главная — Сократить строки — Удалить строки — Удалить пустые строки (Home — Remove Rows — Remove empty rows).
Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована — преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:

Отмена свертывания (unpivot) таблицы

Сворачиваем обратно

Результат

Ссылки по теме

Что такое макрос, как он работает, куда копировать текст макроса, как запустить макрос?
Заполнение всех пустых ячеек в списке значениями вышестоящих ячеек
Удаление всех пустых ячеек из заданного диапазона
Удаление всех пустых строк на листе с помощью надстройки PLEX

Как удалить неиспользуемые строки ниже рабочей области в excel