Here is the macro I wrote:
Sub RemoveNotAddRows()
Dim bDelRow As Boolean
Dim c, r, lastRow As Long
Dim checkVals(30) As Integer
Dim begSumCol, endSumCol As Integer
Dim strTmp As String
Dim wb As Workbook
Dim ws As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")
begSumCol = -99
endSumCol = -99
For c = 1 To 30
strTmp = Left(ws.Cells(1, c), 4)
If strTmp <> "Cell" Then
If begSumCol = -99 Then
begSumCol = c
ElseIf Len(strTmp) = 0 Then
endSumCol = c - 1
Exit For
End If
End If
Next c
For c = begSumCol To endSumCol
checkVals(c) = CInt(ws.Cells(1, c))
Next c
With ws
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For r = lastRow To 2 Step -1
bDelRow = False
For c = begSumCol To endSumCol
If CInt(ws.Cells(r, c)) <> checkVals(c) Then
bDelRow = True
Exit For
End If
Next c
If bDelRow Then
Rows(r).EntireRow.Delete
End If
Next r
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
End With
End Sub
Nothing special or fancy. Here's the issue: When I tried running this on a file with 43,352 rows and 7 columns of sums to match, I had to kill Excel after 10+ minutes because there were no results. However, when I manually checked the sums in two columns, and reduced the number of rows to 2170, the macro finished in 10 seconds. Simple match suggest that the original file should have finished in about three and one half minutes, but that didn't happen. Why?
update: I just ran this macro again on a different file with 'only' 12,046 rows. Based on the above math, this should have finished in under one minute, but it dd not finish until almost five minutes had elapsed.
Bookmarks