Работая с огромным объемом данных в таблицах "Эксель", ненароком можно оставить пустые строки, что в некоторых случаях совсем недопустимо. В этой статье пойдет речь о том, как в Excel удалить пустые строки. Будет представлено четыре способа, которые помогут вам это сделать. Советуется дочитать статью до финала, чтобы в конце принять для себя решение, каким способом пользоваться, так как они в достаточной степени отличаются друг от друга.
Для начала разберемся, как в самым простым способом, который используют практически все неопытные пользователи, но в некоторых случаях именно такое удаление использовать лучше всего.
Итак, чтобы удалить лишние пустые строки в программе, вам необходимо:
После этого появится окошко, в котором необходимо выбрать, что именно нужно удалить. В нашем случае это строки, значит, выбирайте пункт "Строку" и нажимайте "ОК". Теперь все лишние строки будут удалены из таблицы. Однако можно ускорить этот процесс, пропустив этапы по вызову контекстного меню. Для этого просто выделите ненужные строки и нажмите сочетание клавиш "CTRL+-".
Также есть альтернатива. Вы можете после выделения строк перейти во вкладку "Главная" и там на панели инструментов из выпадающего списка "Удалить" выбрать пункт "Удалить строки с листа", что также довольно удобно.
Первый способ, как в Excel удалить пустые строки, мы разобрали. Однако он не очень удобен, если нужно удалить много строк, ведь выделять их придется довольно долго. Именно поэтому мы сейчас расскажем, как в Excel удалить пустые строки при помощи инструмента "Сортировка".
Итак, вам необходимо сделать следующее:
Как только вы сделаете выбор, все пустые строки, которые были в таблице, переместятся в ее низ и их будет легче выделить и удалить, используя способ, который был приведен самым первым.
В Excel удалить повторяющиеся строки, которые пустые, можно при помощи фильтра. Много не разглагольствуя, перейдем непосредственно к самой инструкции:
Вот и все, как видно, третий способ, как удалить лишние строки в Excel, намного удобнее, чем некоторые из предыдущих.
Напоследок расскажем еще об одном интересном способе, как удалить из таблицы пустые строки. На сей раз будем использовать выделение ячеек, которое осуществляется с помощью специального инструмента. Но обо всем по порядку:
Теперь все пустые строки выделены и вы можете их удалить. Для этого, как и в первом способе, нажмите на кнопку "Удалить" в панели инструментов вкладки "Главная".
В этой статье я объясню, почему удаление пустых строк в Excel при помощи выделить пустые ячейки > удалить строку – это плохая идея, и покажу Вам 2 быстрых и правильных способа, как удалить пустые строки не разрушая данные. Все эти способы работают в Excel 2013, 2010 и в более старых версиях.
Если Вы читаете эту статью, то, скорее всего, постоянно работаете в Excel с большими таблицами. Вы знаете, что пустые строки периодически появляются среди данных, ограничивая работу большинства инструментов Excel с таблицами (сортировка, удаление дубликатов, промежуточные итоги и так далее), не позволяя им правильно определять диапазон данных. И каждый раз приходится вручную определять границы, иначе итогом будет неверный результат и уйма потраченного времени на исправление ошибок.
Существует множество причин, почему появляются пустые строки. К примеру, Вы получили рабочую книгу Excel от другого человека или в результате экспорта из корпоративной базы данных, или ненужные данные в строках были удалены вручную. В любом случае, если Ваша цель – удалить все эти пустые строки и получить чистую и аккуратную таблицу, то следуйте простым шагам, приведённым ниже:
Повсюду в интернете Вы можете встретить простой совет, якобы позволяющий удалить пустые строки:
Это очень плохой способ , делайте так только с очень простыми таблицами с парой дюжин строк, которые помещаются на одном экране, а ещё лучше – не делайте так совсем ! Главная причина – если строка с важными данными содержит хотя бы одну пустую ячейку, то удалена будет вся строка .
Например, у нас есть таблица клиентов, всего 6 строк. Мы хотим удалить строки 3 и 5 , потому что они пустые.
Сделайте так, как предложено выше, и получите такой результат:
Строка 4 (Roger) тоже исчезла, потому что ячейка D4 в столбце Traffic source оказалась пуста
Если Ваша таблица не велика, то Вы заметите потерю данных, но в реальных таблицах с тысячами строк Вы можете неосознанно удалить дюжины нужных строк. Если повезёт, Вы обнаружите потерю в течение нескольких часов, восстановите рабочую книгу из резервной копии и продолжите работу. А что если не повезёт, и у Вас не будет резервной копии?
Этот метод работает, если в Вашей таблице есть столбец, который помогает определить, пуст рассматриваемый столбец или нет (ключевой столбец). К примеру, это может быть ID покупателя или номер заказа, или что-то подобное.
Воспользуйтесь этим способом, если в Вашей таблице присутствуют многочисленные пустые ячейки, разбросанные по разным столбцам, а Вам нужно удалить только те строки, которые не имеют ни одной ячейки с данными.
31 ОктДобрый день уважаемые читатели! Очень часто при работе с таблицами нам приходится удалять пустые строки. Когда таблица очень большая это становится реальной проблемой. Можно попробовать их скрыть, но тогда не получится видеть актуальную информацию (по адресам). Поэтому пойдём путём настоящих исследователей — создадим свою команду на удаление пустых строк. Предыдущая статья, посвящённая часам в ячейке —
На просторах интернета можно встретить ответ на поставленный вопрос, но, как правило, он сводится к одному:
Либо предлагается использовать фильтр. С помощью него выделить пустые строки, а затем их удалить.
Плюсом обоих способов является их «встроенность» в программу. Минусы — большое количество действий и, если таблица, большая, то сложно проверить результат. Но, как говорится, мы не ищем простых путей.
Как всегда напишем небольшой макрос. Его особенностью будет сам алгоритм нахождения пустых строк. Обычно макросы анализируют данные по строкам сверху вниз. Мы же заставим сделать наоборот — снизу вверх. Для того, чтобы не учитывать сдвиг удаляемых пустых строк. Таблица заготовка уже должна быть.
Вставим в наш документ новый модуль:
Sub DeleteEmptyStrings ()
Dim intRow as Integer
Dim intLastRow as Integer
‘получение номера последней строки с данными
intLastRow = ActiveSheet.UsedRange.Row + _
ActiveSheet.UsedRange.Rows.Count — 1
‘Удалить пустые строки
For intRow = IntLastRow To 1 Step -1
If ActiveSheet.Rows(intRow).Text = «» Then
ActiveSheet.Rows(intRow).Delete
End If
Next intRow
End Sub
Немного пояснений. Первым делом объявляем две переменные — Row (строка) и LastRow (последняя строка). Тип целое.
Следующей строчкой мы показываем макросу найти последнюю строчку с данными на листе.
Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.
Способ первый:
Использовать встроенное средство Excel - фильтр. Сначала его необходимо "установить" на листе:
Теперь выбираем условие для фильтра:
| Dim lCol As Long Dim lLastRow As Long , li As Long Dim lMet As Long Dim arr sSubStr = InputBox(, "Запрос параметра" , "" ) If sSubStr = "" Then lMet = 0 Else lMet = 1 lCol = Val(InputBox(, "Запрос параметра" , 1)) If lCol = 0 Then Exit Sub lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count 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 |
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("Укажите значение, которое необходимо найти в строке", "Запрос параметра", "") If sSubStr = "" Then lMet = 0 Else lMet = 1 lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "Запрос параметра", 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("Укажите значение, которое необходимо найти в строке" , "Запрос параметра" , "" ) lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение" , "Запрос параметра" "цикл с первой строки до конца 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 |
Sub Del_SubStr() Dim sSubStr As String "искомое слово или фраза(может быть указанием на ячейку) Dim lCol As Long "номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim arr sSubStr = InputBox("Укажите значение, которое необходимо найти в строке", "Запрос параметра", "") lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "Запрос параметра", 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("Укажите номер столбца, в котором искать указанное значение" , "Запрос параметра" , 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 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 |
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("Укажите номер столбца, в котором искать указанное значение", "Запрос параметра", 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 CStr(arr(li, 1)) = sSubStr Then
заменить на такую:
| If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then |
If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then
УДАЛЕНИЕ ИЗ ЛИСТА СТРОК, КОТОРЫХ НЕТ В СПИСКЕ ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Т.к. в последнее время стало поступать все больше и больше вопросов как не удалять значения по списку, а наоборот - оставить в таблице только те значения, которые перечислены в списке - решил дополнить статью и таким кодом.
Значения, которые необходимо оставить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - после работы кода на листе будут оставлены только те строки, в которых присутствует хоть одно из перечисленных в списке значений. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.
В отличие от приведенных выше кодов, данный код ориентирован на то, что значения в списке указаны не полностью. Т.е. если необходимо оставить только те ячейки, в которых встречается слово "активы", то в списке надо указать только это слово. В этом случае если в ячейке будет записана фраза "Нематериальные активы" или "Активы сторонние" - эти ячейки не будут удалены, т.к. в них встречается слово "активы". Регистр букв при этом неважен.
| "процедура оставляет в листе только те значения, которые перечислены в списке 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.сайт" , 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 |
"процедура оставляет в листе только те значения, которые перечислены в списке 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..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 Not rr Is Nothing Then rr.EntireRow.Delete
заменить на такую:
For li = 1 To lLastRow "цикл с первой строки до конца
1 - это первая строка; lLastRow - определяется автоматически кодом и равна номеру последней заполненной строки на листе. Если надо начать удалять строки только с 7-ой строки(например, в первых 6-ти шапка), то код будет выглядеть так:
For li = 7 To lLastRow "цикл с седьмой строки до конца
А если надо удалять только с 3-ей по 300-ю, то код будет выглядеть так:
For li = 3 To 300 "цикл с третьей строки до трехсотой
Вы работаете с большим списком номенклатуры, что-то нужно удалить, что-то добавить (для удаления не забывайте пользоваться ‘ ). Но что делать если вы удалили не строки а просто данные в этих строках? И осталась таблица с «пропусками». Как удалить пустые строки в Excel сразу? Как всегда есть сразу несколько способов.
Нам нужно удалить строки 7 и 3, но как правило их гораздо больше. Рассмотрим не «ручные» методы удаления.
Применяйте для нашей таблицы . Главная — Редактирование — Сортировка и фильтр. А затем отбирайте только пустые строки по нужному столбцу
Останутся только пустые строки. Выделяйте и удаляйте.
Способ еще проще, сортируем все ячейки по любому столбцу — пустые строки ушли.
Оригинальный метод, мне он нравиться тем, что даже зная об этой возможности, я ей почти не пользуюсь, но для общего развития напишу:)
Выделяем таблицу. Переходим Главная — Редактирование — Найти и выделить — Выделение группы ячеек. В открывшемся окне выбираем Пустые ячейки.
Выделяться только пустые ячейки — правой кнопкой мыши, удалить строки или на ленте Главная — Ячейки — Удалить.
Внимание! Ячейки, содержащие формулы пустыми считаться не будут.
Как написать макрос читайте . А если умеете создавать, воспользуйтесь уже готовым макросом для удаления всех пустых строк.
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 . Им отдельное спасибо, за все что они делают!
Читайте подробно . А если вкратце то:
Найдите последнюю ячейку в таблице, выделить первую пустую ячейку после нее (а лучше первую пустую строку), нажмите . Так мы выделим все ячейки ниже рабочей таблицы. Теперь удалим их.
Пишите комментарии, если удалить пустые строки не получается.
Поделитесь нашей статьей в ваших соцсетях:
