Hello,
Situation: Accounting database that needs to be matched based on the last two columns. Any unmatched rows are manually fixed.
<tbody>
</tbody>
Logic: When the non-zero amount on column K matches the one in column L, the macro colors the respective rows. The macro skips the already colored cells and only compares the non-zero values. Once the macro attempts to match all the values, a filter is used to show only the non-color cells. These cells are the unmatched ones.
Issue: The macro worked with the original data of 188 rows. Once a bigger database was attempted, about 15000 rows, excel does not respond after a few seconds and crashes. The first time, there was a message that pointed out to this line of my code in my Scan sub:
Macro:
Question: Why does the macro work on a smaller scale but crashes when I use about 15000 rows? Can it be the loop is too slow and process intensive?
Situation: Accounting database that needs to be matched based on the last two columns. Any unmatched rows are manually fixed.
A | B | C | D | E | F | G | H | I | J | K | L | |||||||||
Type | Nbr | Post |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||||
AP | AD | 65465 | 65464 | X | Y | 4-26 | XXX | sdfsdfs | asdfd |
<tbody> </tbody> |
<tbody> </tbody> | |||||||||
AP | AD | 65481 | 21548 | X | Y | 4-12 | XXX | sdfsdf | adfas | 0 | 54152 | |||||||||
AP | VO | 46481 | 54123 | X | Y | 5-3 | XXX | asdfasd | adsfa | 43053 | 0 |
<tbody>
</tbody>
Logic: When the non-zero amount on column K matches the one in column L, the macro colors the respective rows. The macro skips the already colored cells and only compares the non-zero values. Once the macro attempts to match all the values, a filter is used to show only the non-color cells. These cells are the unmatched ones.
Issue: The macro worked with the original data of 188 rows. Once a bigger database was attempted, about 15000 rows, excel does not respond after a few seconds and crashes. The first time, there was a message that pointed out to this line of my code in my Scan sub:
Code:
If (Cells(i, 11).Interior.ColorIndex = xlNone) And (Cells(m, 12).Interior.ColorIndex = xlNone) Then
Macro:
Code:
Private Sub CommandButton1_Click()
screenUpdateState = Application.ScreenUpdating
StatusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting
'turn off some Excel functionality for efficiency
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
Call clear_formats
Call Scan
Call filter
'Resets settings
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting
End Sub
Private Sub Scan()Dim Rows1 As Integer
Dim Rows2 As Integer
Dim lastRow As Integer
Dim i As Integer
Dim m As Integer
i = 1
m = 1
'Find number of rows with data
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'Scan first column
For i = 2 To lastRow
For m = 2 To lastRow
If Cells(i, 11).Value <> 0 And Cells(m, 12).Value <> 0 Then
'Check for a color
If (Cells(i, 11).Interior.ColorIndex = xlNone) And (Cells(m, 12).Interior.ColorIndex = xlNone) Then
'looks for a match
If Cells(i, 11).Value = Cells(m, 12).Value Then
Rows(i).Interior.ColorIndex = 42
Rows(m).Interior.ColorIndex = 42
End If
End If
End If
Next m
Next i
End Sub
Private Sub filter()
'filter by colorindex = 42 in column 11
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$100288").AutoFilter Field:=11, Operator:= _
xlFilterNoFill
End Sub
Private Sub clear_formats()
'Reset filter
Worksheets("Test").AutoFilterMode = False
'Reset formats of whole page
Cells.Select
Selection.ClearFormats
Range("A1").Select
End Sub
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Call clear_formats
Application.ScreenUpdating = True
End Sub
Question: Why does the macro work on a smaller scale but crashes when I use about 15000 rows? Can it be the loop is too slow and process intensive?