Speeding up Macro

[rchansen2001]

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.

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

 

[Sektor]

Excel often freezes when I run it

It will freeze until you call DoEvents method.
Also, get rid of all With...End With statements - they don't need to be here. With..End With always creates hidden variable.
So, we get:

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
    
    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 = Cells(lX, lLastDateColumn - 2).Font.Underline <> xlUnderlineStyleNone
        bB = Cells(lX, lLastDateColumn - 1).Font.Underline <> xlUnderlineStyleNone
        bC = Cells(lX, lLastDateColumn).Font.Underline <> xlUnderlineStyleNone
        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
        
    Application.StatusBar = False
    Application.ScreenUpdating = True
    
    
End Sub