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

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

Какой самый быстрый способ сделать это?

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

Источник

4 ответа

Решение

Вот процедура, которую я использую для удаления пустых строк таблицы. Я иногда работаю с большими (для Excel) наборами данных, и этот метод работает быстрее. Он загружает строки таблицы в массив, проверяет массив на наличие пустых строк и выполняет один range.delete операция в конце.

Вы используете процедуру, как это:

Sub Test()
    DeleteBlankTableRows ActiveSheet.ListObjects(1)
End Sub

ActiveSheet.ListObjects (1) является (обычно) первой таблицей таблицы на активном рабочем листе.

Вот фактическая процедура:

Sub DeleteBlankTableRows(ByVal tbl As ListObject)
    Dim rng As Range
    Set rng = tbl.DataBodyRange ' Get table data rows range.
    Dim DirArray As Variant
    DirArray = rng.Value2       ' Save table values to array.

    ' LOOP THROUGH ARRAY OF TABLE VALUES
    Dim rowTMP As Long
    Dim colTMP As Long
    Dim combinedTMP As String
    Dim rangeToDelete As Range

    '  Loop through rows.
    For rowTMP = LBound(DirArray) To UBound(DirArray)
        combinedTMP = vbNullString  ' Clear temp variable.

        ' Loop through each cell in the row and get all values combined.
        For colTMP = 1 To tbl.DataBodyRange.Columns.Count
            combinedTMP = combinedTMP & DirArray(rowTMP, colTMP)
        Next colTMP

        ' Check if row is blank.
        If combinedTMP = vbNullString Then
            ' Row is blank.  Add this blank row to the range-to-delete.
            If rangeToDelete Is Nothing Then
                Set rangeToDelete = tbl.ListRows(rowTMP).Range
            Else
                Set rangeToDelete = Union(rangeToDelete, tbl.ListRows(rowTMP).Range)
            End If
        End If
    Next rowTMP

    ' DELETE BLANK TABLE ROWS (if any)
    If Not rangeToDelete Is Nothing Then rangeToDelete.Delete
End Sub

Это имеет некоторые преимущества перед другими методами:

  1. СКОРОСТЬ: при тестировании таблицы с 200000 строк и 8 столбцов этот метод занял 19 секунд. Это чуть больше половины 34 секунд SpecialCells(xlCellTypeBlanks) метод, необходимый для идентичной таблицы.
  2. Идентифицирует полностью пустые строки таблицы: в отличие от некоторых других методов (таких как этот, который на самом деле очень полезен в некоторых ситуациях), этот метод ищет пробелы в каждой ячейке строки вместо одного.
Источник

Это должно работать. Не уверен, что это быстрее, но это еще один способ сделать это:

Sub delete_blank_table_rows()
Dim Rng As Range, tempRng As Range
Set Rng = Range("Table1")    ' Change as necessary
Set Rng = Range(Cells(Rng.Rows(1).Row, Rng.Columns(1).Column), Cells(Rng.Rows(Rng.Rows.Count).Row, Rng.Columns(Rng.Columns.Count).Column))

Dim i       As Long
For i = Rng.Rows.Count To 1 Step -1
    Cells(Rng.Rows(i).Row, Rng.Columns(1).Column).Select
    Set tempRng = Range(Cells(Rng.Rows(i).Row, Rng.Columns(1).Column), Cells(Rng.Rows(i).Row, Rng.Columns(Rng.Columns.Count).Column))

    If WorksheetFunction.CountA(tempRng) = 0 Then
        tempRng.Delete shift:=xlUp
    End If

Next i
End Sub

Изменить: И, конечно, чтобы ускорить его, вы должны отключить Обновление экрана, Расчет во время его работы.

Источник

Я думаю, что это может быть быстрее (вы можете изменить lastrow и lastcol в соответствии с размерами таблицы):

Sub delete_rows_blank2()

t = 1
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count

Do Until t = lastrow

For j = 1 To lastcol

    If Cells(t, j) = "" Then

        j = j + 1

            If j = lastcol Then
            Rows(t).Delete
            t = t + 1
            End If

    Else

        t = t + 1

    End If

Next

Loop

End Sub
Источник

Вот фрагмент кода, который работает для меня. У меня есть таблица с именем «Метрики», которая находится на листе и связана с переменной metricsWKS . Я использую фильтр таблицы, чтобы выбрать строки, которые нужно удалить. В моем случае это определяется пустыми значениями как для полей клиента, так и для поля задания. Единственная сложная часть — это выбор поля. До сих пор не хочется верить, что смещение работает только с видимыми ячейками и пропускает скрытые фильтром строки.

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

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

      Dim metricsWKS As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlManual

' eliminate rows that have a blank client and a blank job
With metricsWKS.ListObjects("Metrics")
   .Range.AutoFilter Field:=.ListColumns("Client").Index, Criteria1:="=" ' client name is blank
   .Range.AutoFilter Field:=.ListColumns("Job Name").Index, Criteria1:="=" ' job name is blank
End With
Range("Metrics[[#Headers],[Task Name]]").Offset(1).Select ' select the first blank row under the header row
Range(Selection, Selection.End(xlDown)).Select ' add in all the other blank rows through the bottom of the filtered table
Selection.EntireRow.Delete ' delete all the rows
metricsWKS.ShowAllData ' clear the filter

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Источник
Другие вопросы по тегам