I’m providing the first answer as a reference
Others may find it useful, if there are no other options available
- Fastest way to achieve the result is not to use the Delete operation
- Out of 1 million records it removes 100,000 rows in an average of 33 seconds
.
Sub DeleteRowsWithValuesNewSheet() '100K records 10K to delete
'Test 1: 2.40234375 sec
'Test 2: 2.41796875 sec
'Test 3: 2.40234375 sec
'1M records 100K to delete
'Test 1: 32.9140625 sec
'Test 2: 33.1484375 sec
'Test 3: 32.90625 sec
Dim oldWs As Worksheet, newWs As Worksheet, rowHeights() As Long
Dim wsName As String, t As Double, oldUsedRng As Range
FastWB True: t = Timer
Set oldWs = Worksheets(1)
wsName = oldWs.Name
Set oldUsedRng = oldWs.Range("A1", GetMaxCell(oldWs.UsedRange))
If oldUsedRng.Rows.Count > 1 Then 'If sheet is not empty
Set newWs = Sheets.Add(After:=oldWs) 'Add new sheet
With oldUsedRng
.AutoFilter Field:=1, Criteria1:="<>Test String"
.Copy 'Copy visible data
End With
With newWs.Cells
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteAll 'Paste data on new sheet
.Cells(1, 1).Select 'Deselect paste area
.Cells(1, 1).Copy 'Clear Clipboard
End With
oldWs.Delete 'Delete old sheet
newWs.Name = wsName
End If
FastWB False: InputBox "Duration: ", "Duration", Timer - t
End Sub
.
At high level:
- It creates a new worksheet, and keeps a reference to the initial sheet
- AutoFilters column 1 on the searched text:
.AutoFilter Field:=1, Criteria1:="<>Test String"
- Copies all (visible) data from initial sheet
- Pastes column widths, formats, and data to the new sheet
- Deletes initial sheet
- Renames the new sheet to the old sheet name
It uses the same helper functions posted in the question
The 99% of the duration is used by the AutoFilter
.
There are a couple limitations I found so far, the first can be addressed:
-
If there are any hidden rows on the initial sheet, it unhides them
- A separate function is needed to hide them back
- Depending on implementation, it might significantly increase duration
-
VBA related:
- It changes the Code Name of the sheet; other VBA referring to Sheet1 will be broken (if any)
- It deletes all VBA code associated with the initial sheet (if any)
.
A few notes about using large files like this:
- The binary format (.xlsb) reduce file size dramatically (from 137 Mb to 43 Mb)
-
Unmanaged Conditional Formatting rules can cause exponential performance issues
- The same for Comments, and Data validation
-
Reading file or data from network is much slower than working with a locall file