rchansen2001
New Member
- Joined
- Nov 30, 2016
- Messages
- 28
I am trying to speed up my macro, it takes almost 5 minutes to run and Excel often freezes when I run it. The macro is comparing the data between 3 columns and 10-20 thousand rows, so it has to process a lot of data. Any help is appreciated.
Code:
Sub JNP()
Dim lX As Long
Dim lLastDataRow As Long
Dim lLastDateColumn As Long
Dim bA As Boolean
Dim bB As Boolean
Dim bC As Boolean
Application.ScreenUpdating = False
With ActiveSheet
lLastDateColumn = .Cells(1, Columns.Count).End(xlToLeft).Column - 2
lLastDataRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For lX = 2 To lLastDataRow
Application.StatusBar = lX & " " & lLastDataRow
bA = False: bB = False: bC = False
With .Cells(lX, lLastDateColumn - 2)
If .Font.Underline <> xlUnderlineStyleNone Then bA = True
End With
With .Cells(lX, lLastDateColumn - 1)
If .Font.Underline <> xlUnderlineStyleNone Then bB = True
End With
With .Cells(lX, lLastDateColumn)
If .Font.Underline <> xlUnderlineStyleNone Then bC = True
End With
If .Cells(lX, lLastDateColumn - 2) = .Cells(lX, lLastDateColumn - 1) And _
.Cells(lX, lLastDateColumn - 2) = .Cells(lX, lLastDateColumn) And _
bA And bB And bC Then
'DO NOTHING BECAUSE WE WANT TO SEE THE BAD ONES
Else
Rows(lX).Hidden = True ' HIDE THE GOOD ONES
End If
Next
End With
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
Last edited: