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

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

Навык работы с программой Excel определенно полезен представителям многих профессий. В данной статье рассмотрим ряд вопросов – в частности, почему убирать строки в Excel посредством схемы “выделение пустых ячеек -> удаление строки” – не самая лучшая идея . А также разберем 3 быстрых и надежных метода удаления пустых строк таким образом, чтобы это никак не повредило информацию в других ячейках. Все решения применимы к Excel 2019, 2016, 2013, а также более ранним выпускам.

3 быстрых и надежных метода удаления пустых строк

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

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

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

Не убирайте пустые строки посредством выбора пустых ячеек

На просторах интернета вы часто можете встретить следующий совет:

  • Выберите все ячейки, где содержится информация, с 1-ой по последнюю .
  • Нажмите клавишу F5 – в результате появится диалоговое окно « Переход ».
  • Внутри открывшегося окна щелкните « Выделить ».
  • В окне « Выделение группы ячеек » выберите вариант « пустые ячейки », затем « ОК ».
  • Правой кнопкой мышки щелкните на любой выбранной ячейке, затем выберите пункт « Удалить… ».
  • Внутри открывшегося окна « Удаление ячеек » кликните на вариант « строку ».

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

Это связано с тем, что, если в строке, где находится важная информация, есть лишь одна пустая ячейка, строка удаляется целиком .

Давайте рассмотрим на примере. Перед нами таблица клиентов, в ней только 6 строк . Нам хочется удалить третью и пятую строки , так как они пустые.

Воспользуйтесь описанным выше методом, и вы получите следующее:

Строка 4 (Rojer) также пропала , поскольку в столбце «Источник трафика» («Traffic source») ячейка D4 является пустой .

Так как у вас небольшая таблица, вы сможете обнаружить отсутствие данных , но в больших таблицах, которые содержат тысячи строк, вы можете удалять десятки нужных строк, даже не подозревая об этом. Если вам повезет, вы заметите пропажу за несколько часов, восстановите свой файл из резервной копии , а затем снова проделаете эту работу. Но что делать, если вам не повезло, или у вас нет резервной копии?

Давайте разберем 3 быстрых и надежных способа удаления пустых строк из ваших таблиц Excel. А если вы хотите сэкономить свое время – переходите сразу к 3-му методу .

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

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

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

  1. Выберите таблицу полностью, с 1-й по последнюю строку (для этого можно зажать одновременно Ctrl + Home , далее – Ctrl + Shift + End ).
  1. Установите Автофильтр : перейдите на вкладку « Данные » и кликните на кнопку « Фильтр ». Как можно удалить в Excel все пустые строки автоматически
  1. Далее необходимо к столбцу «Cust #» («Клиент №») применить фильтр: кликните на стрелку выпадающего списка «Автофильтр» в названии столбца, уберите флажок (Выделить все) , прокрутите вниз до конца (на самом деле список довольно длинный), затем отметьте флажком пункт «Пустые» . Кликните ОК . Как можно удалить в Excel все пустые строки автоматически
  1. Объедините все отфильтрованные строки: для этого можно зажать одновременно Ctrl + Home , далее – кнопку со стрелкой, направленной вниз, чтобы снова вернуться к первой строке, далее зажмите Ctrl + Shift + End .
  1. Щелкните правой кнопкой мышки по какой-либо выбранной ячейке и нажмите на « Удалить строку » либо просто зажмите Ctrl + – (знак минус) . Как можно удалить в Excel все пустые строки автоматически
  1. Кликните на кнопку OK при ответе на вопрос « Удалить всю строку листа? » Как можно удалить в Excel все пустые строки автоматически
  1. Затем нужно очистить примененный фильтр: для этого перейдите во вкладку « Данные » и кликните на кнопку « Очистить ». Как можно удалить в Excel все пустые строки автоматически
  1. Хорошая работа ! Все пустые строки удалились, и третья строка (Rojer) никуда не делась ( для сравнения можно обратиться к предыдущему варианту ).

Устранение всех пустых строк, когда отсутствует ключевой столбец

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

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

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

  1. Создайте столбец «Blanks» («Пустые ячейки») в самом конце своей таблицы, затем в 1-ую ячейку этого столбца запишите формулу : = COUNTBLANK (A2: C2).

Данная формула считает пустые ячейки в прописанном диапазоне , где А2 – 1-ая ячейка, C2 – последняя ячейка.

  1. Скопируйте формулу для всех ячеек столбца.
  1. Теперь мы имеем ключевой столбец . Затем используйте фильтр в столбце «Blanks» («Пустые ячейки») (подробные инструкции указаны выше), чтобы отобразить строки с максимальным значением (3). “3” обозначает следующее: все ячейки в ряду являются пустыми. Как можно удалить в Excel все пустые строки автоматически
  1. Затем выберите все отфильтрованные строки и уберите полностью пустые , пользуясь описанными ранее инструкциями.

Таким образом пустая строка (строка 5) удаляется , а строки с необходимой информацией остаются на месте .

  1. Далее удаляем дополнительный столбец . Он больше не потребуется. Либо можно поставить другой фильтр и отобразить строки, где имеется одна пустая ячейка или несколько.

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

Для этого уберите флажок « 0 », затем кликните « ОК ».

Применение инструмента «Удалить пустые строки» – самый удобный и быстрый метод

Самый быстрый и безупречный метод удаления пустых строк – это инструмент « Удалить пустые строки », входящий в комплект Ultimate Suite для программы Excel .

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

Как удалить пустые строки за 4 простых шага

Используя Ultimate Suite, дополнительно установленный к программе Excel, вот что необходимо сделать :

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

  1. Нажмите на любую ячейку в таблице.
  2. Перейдите на вкладку Инструменты Ablebits >Трансформировать группу .
  3. Нажмите Удалить пустые строки >Пустые строки .
  1. Нажмите кнопку ОК , чтобы подтвердить, что вы действительно хотите удалить пустые строки.

Вот и все ! Всего несколько кликов, и вы получите чистую таблицу , все пустые строки пропали, а порядок строк не искажен!

Как удалить строки по условию?

Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.

Способ первый:
Использовать встроенное средство Excel — фильтр. Сначала его необходимо "установить" на листе:

  • Выделяем таблицу с данными, включая заголовки. Если их нет — то выделяем с самой первой строки таблицы, в которой необходимо удалить данные
  • устанавливаем фильтр:
  • для Excel 2003 : ДанныеФильтрАвтофильтр
  • для Excel 2007-2010 : вкладка Данные (Data)Фильтр (Filter)(или вкладка Главная (Home)Сортировка и фильтр (Sort&Filter)Фильтр (Filter) )

Теперь выбираем условие для фильтра:

  • в Excel 2003 надо выбрать Условие и в появившейся форме выбрать непосредственно условие("равно", "содержит", "начинается с" и т.д.), а напротив значение в соответствии с условием.
  • Для 2007-2010 Excel нужно выбрать Текстовые фильтры (Text Filters) и либо сразу выбрать одно из предлагаемых условий, либо нажать Настраиваемый фильтр (Custom Filter) и ввести значения для отбора в форме

После этого удалить отфильтрованные строки. В 2007 Excel могут возникнуть проблемы с удалением отфильтрованных строк, поэтому рекомендую сначала так же прочитать статью: Excel удаляет вместо отфильтрованных строк — все?! Как избежать.

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

Sub Del_SubStr() Dim sSubStr As String ‘искомое слово или фраза(может быть указанием на ячейку) Dim lCol As Long ‘номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim lMet As Long Dim arr sSubStr = InputBox("Укажите значение, которое необходимо найти в строке", "www.excel-vba.ru", "") If sSubStr = "" Then lMet = 0 Else lMet = 1 lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1)) If lCol = 0 Then Exit Sub lLastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count arr = Cells(1, lCol).Resize(lLastRow).Value Application.ScreenUpdating = 0 Dim rr As Range For li = 1 To lLastRow ‘цикл с первой строки до конца If -(InStr(arr(li, 1), sSubStr) > 0) = lMet Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If Next li If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub

Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
Данный код необходимо поместить в стандартный модуль. Вызвать с листа его можно нажатием клавиш Alt + F8 , после чего выбрать Del_SubStr и нажать Выполнить. Если в данном коде в строке
If -(InStr(Cells(li, 1), sSubStr) > 0) = lMet Then
вместо = lMet указать <> lMet , то удаляться будут строки, не содержащие указанное для поиска значение. Иногда тоже удобно.
Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска "отчет", то будут удалены все строки, в которых встречается это слово("квартальный отчет", "отчет за месяц" и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:

Sub Del_SubStr() Dim sSubStr As String ‘искомое слово или фраза(может быть указанием на ячейку) Dim lCol As Long ‘номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim arr sSubStr = InputBox("Укажите значение, которое необходимо найти в строке", "www.excel-vba.ru", "") lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1)) If lCol = 0 Then Exit Sub lLastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count arr = Cells(1, lCol).Resize(lLastRow).Value Application.ScreenUpdating = 0 Dim rr As Range For li = 1 To lLastRow ‘цикл с первой строки до конца If CStr(arr(li, 1)) = sSubStr Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If Next li If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub

Здесь так же, как и в случае с предыдущим кодом можно заменить оператор сравнения( Cells(li, lCol) = sSubStr ) с равно на неравенство( Cells(li, lCol) <> sSubStr ) и тогда удаляться будут строки, значения ячеек которых не равно указанному.

УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений.
Значения, которые необходимо найти и удалить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений — они все будут удалены. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.

Sub Del_Array_SubStr() Dim sSubStr As String ‘искомое слово или фраза Dim lCol As Long ‘номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long Dim arr lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count ‘заносим в массив значения листа, в котором необходимо удалить строки arr = Cells(1, lCol).Resize(lLastRow).Value ‘Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets("Лист2") ‘Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With ‘удаляем Dim rr As Range For lr = 1 To UBound(avArr, 1) sSubStr = avArr(lr, 1) For li = 1 To lLastRow ‘цикл с первой строки до конца If CStr(arr(li, 1)) = sSubStr Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If DoEvents Next li DoEvents Next lr If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub

Чтобы код выше удалял строки не по точному совпадению слов, а по частичному(например, в ячейке записано "Привет, как дела?", а в списке есть слово "привет" — надо удалить, т.к. есть слово "привет"), то надо строку:

If CStr(arr(li, 1)) = sSubStr Then

заменить на такую:

If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then

‘процедура оставляет в листе только те значения, которые перечислены в списке Sub LeaveOnlyFoundInArray() Dim sSubStr As String ‘искомое слово или фраза Dim lCol As Long ‘номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long Dim arr Dim IsFind As Boolean lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count ‘заносим в массив значения листа, в котором необходимо удалить строки arr = Cells(1, lCol).Resize(lLastRow).Value ‘Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets("Лист2") ‘Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With ‘удаляем Dim rr As Range For li = 1 To lLastRow ‘цикл с первой строки таблицы до конца IsFind = False For lr = 1 To UBound(avArr, 1) ‘цикл по списку значений на удаление sSubStr = avArr(lr, 1) If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then IsFind = True End If DoEvents Next lr ‘если значение таблицы не найдено в списке — удаляем строку If Not IsFind Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If DoEvents Next li If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub

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

If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then

заменить на такую:

If CStr(arr(li, 1)) = sSubStr Then

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

If Not rr Is Nothing Then rr.EntireRow.Delete

заменить на такую:

If Not rr Is Nothing Then rr.EntireRow.Hidden = True

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

For li = 1 To lLastRow ‘цикл с первой строки до конца

1 — это первая строка; lLastRow — определяется автоматически кодом и равна номеру последней заполненной строки на листе. Если надо начать удалять строки только с 7-ой строки(например, в первых 6-ти шапка), то код будет выглядеть так:

For li = 7 To lLastRow ‘цикл с седьмой строки до конца

А если надо удалять только с 3-ей по 300-ю, то код будет выглядеть так:

For li = 3 To 300 ‘цикл с третьей строки до трехсотой

Статья помогла? Поделись ссылкой с друзьями!

Ссылка на основную публикацию