Converting My Macro into Array?

Question

I have done a Macro code where I was able to show rows which contains the word "Overdue" and hide the other rows which contain the word (Completed, Pending, in progress, Delayed". everything was going smoothly but it takes like 2 minutes for the macro to work and the database still empty. I have read that arrays can fix this problem and make the macro work really fast. I have tried to convert my code to array but I was unsuccessful.

Just for your information the first row for me is (18) and the column is (Q) so Q18 which have the first data.

Sub Overdue()
On Error Resume Next

Application.ScreenUpdating = False

Worksheets("Dashboard-Data").Rows.EntireRow.Hidden = False
ltrw = Cells(Rows.Count, "Q").End(xlUp).Row

For i = 2 To ltrw
 
 
 If Cells(i, 17).Value = "Overdue" Then
    Cells(i, 1).EntireRow.Hidden = False
   
    ElseIf Cells(i, 17).Value = "Pending" Then
    Cells(i, 1).EntireRow.Hidden = True
   
    ElseIf Cells(i, 17).Value = "In Progress" Then
    Cells(i, 1).EntireRow.Hidden = True
   
    ElseIf Cells(i, 17).Value = "Completed" Then
    Cells(i, 1).EntireRow.Hidden = True
   
    ElseIf Cells(i, 17).Value = "Delayed" Then
    Cells(i, 1).EntireRow.Hidden = True
       
     ElseIf Cells(i, 17).Value = "Delayed & Overdue" Then
    Cells(i, 1).EntireRow.Hidden = True
   
    Else
   
    Cells.EntireRow.Hidden = False
     
         End If
      Next i


Application.ScreenUpdating = True
End Sub
 

Answer 1

First of all, you can significantly shorten the macro code:

Sub Overdue2()
Dim ltrw As Long, i As Long
On Error Resume Next
    
    Application.ScreenUpdating = False
    Worksheets("Dashboard-Data").Rows.EntireRow.Hidden = False
    ltrw = Cells(Rows.Count, "Q").End(xlUp).Row
    
    For i = 2 To ltrw
        Cells(i, 1).EntireRow.Hidden = (Cells(i, 17).Value <> "Overdue")
    Next i
    Application.ScreenUpdating = True
End Sub

Thus, the program does not need to evaluate all the multiple conditions in the If-Then-ElseIf-Then... This will slightly (quite a bit) speed up the macro.

Not sure if your idea of using arrays can greatly improve the algorithm. I suggest using the built-in Excel filtering mechanism - the code will be short and fast enough:

Sub Overdue3()
Dim ltrw As Long
On Error Resume Next
    Worksheets("Dashboard-Data").Rows.EntireRow.Hidden = False
    ltrw = Cells(Rows.Count, "Q").End(xlUp).Row
    Worksheets("Dashboard-Data").Range("Q17:Q" & ltrw).AutoFilter Field:=1, Criteria1:="Overdue"
End Sub

If you don't want to see the autofilter icon, you can change the code like this:

Sub Overdue4()
Dim ws As Worksheet
Dim ltrw As Long, i As Long
Dim filteredRange As Range, filteredRows As Range
On Error Resume Next
    Set ws = Worksheets("Dashboard-Data")
    ws.Rows.EntireRow.Hidden = False
    ltrw = ws.Cells(Rows.Count, "Q").End(xlUp).Row
    Set filteredRange = ws.Range("Q17:Q" & ltrw)
    filteredRange.AutoFilter Field:=1, Criteria1:="Overdue"
Rem Now let's store in the filteredRows variable the rows that remained visible
    Set filteredRows = filteredRange.SpecialCells(xlCellTypeVisible).Rows
Rem Disable AutoFilter
    filteredRange.AutoFilter
Rem Hide all rows in filteredRange
    filteredRange.Rows.EntireRow.Hidden = True
Rem and unhide the rows that are remembered in the variable
    filteredRows.Rows.EntireRow.Hidden = False
End Sub