Slow Running Macro

[rsmaher]

Hi All,

Hope you may be able to help on the below:

I want to make it easier for users to filter the information for what they need using some macro buttons (some struggle with using basic filters); however, it is on a protected and shared workbook so I can't use a VBA filter.

I've created some code to hide the rows that are not required instead.

The macros do work but my problem is that they run slow so I was wondering if I'm missing something or if there is a different type of code that would be more efficient.

Below are a couple of examples.

Example 1:

Sub ReplenFilter()
    BeginRow = 5
    EndRow = 204
    ChkCol = 7
Range("A5:A204").EntireRow.Hidden = False
Application.ScreenUpdating = False    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "Replenishment" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
    
Application.ScreenUpdating = True
    
End Sub

Example 2:

Sub SortationFilter()
    BeginRow = 5
    EndRow = 204
    ChkCol = 7
    
Application.ScreenUpdating = False
Range("A5:A204").EntireRow.Hidden = False
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "Sortation" Or Cells(RowCnt, ChkCol).Value = "Sorter Run" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
    
Application.ScreenUpdating = True
    
End Sub

Thank you in advance.

 

[jmacleary]

If you have a filter set on the range you are working with, then it will be slowed down by a recalculate being done every time there is a change between false and true. Insert ' Application.Calculation = xlManual' at the beginning and 'Application.Calculation = xlAutomatic' at the end and it will speed ap

 

[rsmaher]

Worked like a charm, thanks for that