Speed up HIDE ROWS VBA in Excel 2016

[alecwarner]

The below code runs pretty fast (few seconds) in Excel 2013, but in Excel 2016 it can be a few minutes.

Does anyone know how to speed up this macro?

Thanks

Al

Sub Hide_Rows_Extra()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheet17.Rows("62:82").EntireRow.AutoFit

BeginRow = 62
EndRow = 82
ChkCol = 5
For RowCnt = BeginRow To EndRow
If Sheet17.Cells(RowCnt, ChkCol).Value = 0 Or Sheet17.Cells(RowCnt, ChkCol).Value = "" Then
Sheet17.Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Sheet17.Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt



Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[JackDanIce]

Try:

Sub Hide_Rows_Extra1()
    
    Const start_row As Long = 62
    Const check_col As Long = 5
    Const end_row   As Long = 82
    
    Dim r           As Range
    Dim x           As Long
    
    Application.ScreenUpdating = False
        
    With Sheet17
        With .Cells(start_row, 1).Resize(end_row - start_row + 1).EntireRow
            .AutoFit
            .Hidden = False
        End With
        For x = start_row To end_row
            If Len(.Cells(x, check_col).Value) = 0 Or .Cells(x, check_col).Value = 0 Then
                If Not r Is Nothing Then
                    Set r = Union(r, .Cells(x, check_col))
                Else
                    Set r = .Cells(x, check_col)
                End If
            End If
        Next x
        If Not r Is Nothing Then
            r.EntireRow.Hidden = True
            Set r = Nothing
        End If
    End With


    Application.ScreenUpdating = True
    
End Sub

 

[alecwarner]

Many Thanks

Your suggestion works a lot faster.

Cheers

Al

 

[JackDanIce]

You're welcome