Converting My Macro into Array?

[MAlhash]

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

 

[kevin9999]

If your "first row" with data is 18, why are you starting from row 2? Could you post a sample of your sheet using the XL2BB add in?

 

[kweaver]

I put 200 rows of randomly generated options like you described and ran the following macro. It was instantaneous.
I then ran it with 400 rows and it was also instantaneous (well, maybe 2 seconds). I began in row 2, however.

Sub Overdue2()
On Error Resume Next
Application.ScreenUpdating = False
Dim ltrw As Long, i As Long

ltrw = Cells(Rows.Count, "Q").End(xlUp).Row
Application.ScreenUpdating = False
For i = ltrw To 2 Step -1
 If Cells(i, 17).Value = "Overdue" Then
  Cells(i, 1).EntireRow.Hidden = False
 Else
  If Cells(i, 17) = "Pending" Or Cells(i, 17) = "In Progress" Or Cells(i, 17) = "Completed" Or Cells(i, 17) = "Delayed" Or Cells(i, 17) = "Delayed & OverDue" Then
   Cells(i, 1).EntireRow.Hidden = True
End If
End If
Next i
Application.ScreenUpdating = True
End Sub

 

[Georgiboy]

Could you use AutoFilter and just filter the results?

 

[dmt32]

Hi
see if this update to your code works any quicker for you

Sub OverDue()
    Dim arr                 As Variant
    Dim lr                   As Long
    Dim DataRange   As Range, HideRange As Range, c As Range
    
    arr = Array("Pending", "In Progress", "Completed", "Delayed", "Delayed & OverDue")
    
    With ThisWorkbook.Worksheets("Dashboard-Data")
        lr = .Cells(.Rows.Count, "Q").End(xlUp).Row
        Set DataRange = .Cells(2, "Q").Resize(lr)
    End With
    
    DataRange.EntireRow.Hidden = False
    
    For Each c In DataRange.Cells
        If Not IsError(Application.Match(c.Value, arr, 0)) Then
            If HideRange Is Nothing Then
                Set HideRange = c
            Else
                Set HideRange = Union(HideRange, c)
            End If
        End If
    Next c
    If Not HideRange Is Nothing Then HideRange.EntireRow.Hidden = True
End Sub

Dave

 

[MAlhash]

Thank you all. it is now faster.
Many thanks.

 

[dmt32]

Thank you all. it is now faster.
Many thanks.

most welcome glad we were able to help & appreciate your feedback

Dave