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

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

Я взял Range размером 90`000 рядов на 5 колонок. Заполнил его случайным образом так, чтобы 1-2% строк были пустыми. Кроме того, чтобы 4% ячеек были пустыми.

В таком случае лучше всего работал алгоритм Hugo121: загрузить ячейки в массив и вручную (без формул и встроенных функций) все найти. Затем вернуть массивы в ячейки и удалить помеченные ряды с помощью ColumnDifferences().

Почти не отстает от него алгоритм toiai, который пытается с помощью встроенного метода SpecialCells(xlCellTypeBlanks) выбрать для обработки только те ряды, в которых есть пустые ячейки. Таким образом отфильтровав некоторую часть рядов. Но беда в том, что метод SpecialCells(xlCellTypeBlanks) при большом количестве разделенных областей перестает выбирать пустые ячейки и просто возвращает тот Range для которого его применили. Но и если он срабатывает правильно - это все равно не сильно ускоряет работу (на моем тестовом примере).

Следующий по скорости идет простой обход всех рядов подряд и сбор через Union. А затем удаление. Это вариант комрадов Catstail и ikki. Он простой и надежный, но на моем тестовом примере работает в два раза медленнее, чем фаворит.

Хуже всех показал себя мой вариант, который даже стыдно сказать насколько медленнее

На порядок - это как минимум.

На методе SpecialCells(xlCellTypeBlanks) мне удалось создать еще один вариант, который был даже на четверть быстрее фаворита и довольно прост в записи. Но, как я уже говорил, метод SpecialCells(xlCellTypeBlanks) при большом количестве пробелов возвращает борщ и не только не ускоряет, но и приводит к ошибочному результату.

Так что вот победитель в моей интерпретации:

Visual BasicВыделить код

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

Public Sub FastDeleteEmptyRows(Optional ByVal Sheet As Worksheet)
    
    'SpeedUp
    
    If Sheet Is Nothing Then Set Sheet = ActiveSheet
    
    With Sheet
        
        Dim a(): a = .UsedRange
       
        ReDim b(1 To UBound(a), 1 To 1)
        
        Dim FirstFreeColumn%: FirstFreeColumn = .UsedRange.Columns.Count + 1
        
        Dim Row&
        For Row = 1 To UBound(a)
        
            If Not AllColumnsAreEmpty(a, Row) Then
                b(Row, 1) = 1
            End If
        Next Row
        
        .Cells(.UsedRange.Row, FirstFreeColumn).Resize(UBound(b)) = b
        
        Dim x As Range: Set x = .UsedRange.Columns(FirstFreeColumn).Find(1, lookat:=xlWhole)
        If Not x Is Nothing Then
            .UsedRange.Columns(FirstFreeColumn).ColumnDifferences(x).EntireRow.Delete
        End If
        .UsedRange.Columns(FirstFreeColumn).Clear
       
    End With
    
    'SpeedDown
End Sub
 
Private Function AllColumnsAreEmpty(a(), ByVal Row&) As Boolean
    Dim Column&
    For Column = LBound(a, 2) To UBound(a, 2)
        If Not IsEmpty(a(Row, Column)) Then
            AllColumnsAreEmpty = False
            Exit Function
        End If
    Next Column
    AllColumnsAreEmpty = True
End Function

PS:

Алгоритм предназначен для

не очень разреженных таблиц

.

Если в вашей таблице много пустых рядов (более 2%) - используйте его

на свой страх и риск

.

Выводы

, которые я сделал в ходе исследований:

Для больших таблиц встроенные функции Excel не предназначены и использовать их надо на свой страх и риск. Это касается и Union, и ColumnDifferences и SpecialCells.

Работа с памятью намного быстрее, чем работа с ячейками.

Одно большое удаление работает намного быстрее, чем несколько маленьких.