VBA Excel. Удаление пустых строк

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

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

Содержание

Удаление пустых строк в используемом диапазоне

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

Определить границы используемого диапазона на рабочем листе из кода VBA Excel нам поможет последняя ячейка используемого диапазона: Cells.SpecialCells(xlLastCell).

Самый простой код удаления пустых строк

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

Пример кода VBA Excel для активного листа:

Sub Primer1()

Dim n As Long, i As Long

'Определяем номер строки последней ячейки

'используемого диапазона на рабочем листе

n = Cells.SpecialCells(xlLastCell).Row

'Ищем и удаляем пустые строки

For i = n To 1 Step -1

If Rows(i).Text = "" Then Rows(i).Delete

End Sub

То же самое, но с указанием книги и рабочего листа:

Sub Primer2()

Dim n As Long, i As Long

With ThisWorkbook.Worksheets("Лист1")

n = .Cells.SpecialCells(xlLastCell).Row

For i = n To 1 Step -1

If .Rows(i).Text = "" Then .Rows(i).Delete

End With

End Sub

Программа определения времени выполнения макроса показала, что этот код отработал в диапазоне из 3000 строк за 17,5 секунд.

Улучшенный код удаления пустых строк

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

Sub Primer3()

Dim n As Long, i As Long, myRange As Range

'Присваиваем объектной переменной ссылку на диапазон от первой ячейки

'рабочего листа до последней ячейки используемого диапазона

Set myRange = Range(Range("A1"), Cells.SpecialCells(xlLastCell))

With myRange

n = .Rows.Count

For i = n To 1 Step -1

If .Rows(i).Text = "" Then .Rows(i).Delete

End With

End Sub

Программа определения времени выполнения макроса показала, что этот код отработал в диапазоне из 3000 строк за 13,3 секунды.

Удаление строк по пустым ячейкам

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

Sub Primer4()

Dim n As Long, i As Long

n = Cells.SpecialCells(xlLastCell).Row

For i = n To 1 Step -1

If Cells(i, 1).Text = "" Then Rows(i).Delete

End Sub

или так:

Sub Primer5()

Dim n As Long, i As Long, myRange As Range

Set myRange = Range(Range("A1"), Cells.SpecialCells(xlLastCell))

With myRange

n = .Rows.Count

For i = n To 1 Step -1

If .Cells(i, 1).Text = "" Then .Rows(i).Delete

End With

End Sub

В этих примерах поиск пустой ячейки производится в первом столбце: Cells(i, 1).

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

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

Удаление полностью пустых строк в пределах заданного диапазона:

Sub Primer6()

Dim n As Long, i As Long

With Selection

n = .Rows.Count

For i = n To 1 Step -1

If .Rows(i).Text = "" Then .Rows(i).Delete

End With

End Sub

Удаление строк по пустым ячейкам в одном из столбцов:

Sub Primer7()

Dim n As Long, i As Long

With Selection

n = .Rows.Count

For i = n To 1 Step -1

If .Cells(i, 1).Text = "" Then .Rows(i).Delete

End With

End Sub

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

Илья 29.03.2023 в 10:07

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

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

Sub Split1(NN) Dim r1 As String Dim r2 As String r1 = Split(NN, ":")(0) r2 = Split(NN, ":")(1) Range(r1).Value2 = Range(r1).Value2 & " " & Range(r2).Value2 sd = Split(r2, "K")(1) sd = sd + ":" + sd Rows(sd).Select Range(sd).Activate Selection.Delete Shift:=xlUp End Sub

NN это диапазон строк туда приходит предыдущая строка и последняя K0000011:K0000012
ну а дальше уже понятно.
Но такая реализация не устраивает, потому что отчет строиться черех SAP/ABAP там есть конструктивная особенность, поэтому перебор решил сделать вконец по всем строкам, так что передавать NN уже не будем.

4 комментария для “VBA Excel. Удаление пустых строк”

0mega 18.02.2023 в 09:09

Здравствуйте
Как удалить диапазон строк с переменным значением
f=4
n=12
какую прописать команду для удаления с 4-ой по 12-ую строку?
Rows(f:n).delete
Такой код машине не нравится
1. Евгений 18.02.2023 в 12:44
  
  Добрый день!
  
  1
  
  Rows(f & ":" & n).Delete
2. 1. 0mega 20.02.2023 в 05:29
    
    спасибо
Илья 29.03.2023 в 10:07

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

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

Sub Split1(NN) Dim r1 As String Dim r2 As String r1 = Split(NN, ":")(0) r2 = Split(NN, ":")(1) Range(r1).Value2 = Range(r1).Value2 & " " & Range(r2).Value2 sd = Split(r2, "K")(1) sd = sd + ":" + sd Rows(sd).Select Range(sd).Activate Selection.Delete Shift:=xlUp End Sub

NN это диапазон строк туда приходит предыдущая строка и последняя K0000011:K0000012
ну а дальше уже понятно.
Но такая реализация не устраивает, потому что отчет строиться черех SAP/ABAP там есть конструктивная особенность, поэтому перебор решил сделать вконец по всем строкам, так что передавать NN уже не будем.

Обсуждение закрыто.