Speed up vba macro

[Wamhoi]

So, I'm trying to hide rows based on an N/A value and wrote the following code.

For Each c In Sheets("Summary").Range("b5:b5000")
If c.Value = "N/A" Then c.EntireRow.Hidden = True
Next c

However, its very slow and takes too much time. Do you have any ideas how I can speed up the process?

 

[njimack]

Happy using Autofilter?

ActiveSheet.Range("$A$5:$B$5000").AutoFilter Field:=2, Criteria1:="<>N/A", _
Operator:=xlAnd

Alternatively, try setting calculation to xlmanual and screenupdating to false.

 

[Leith Ross]

Hello Wamhoi,

Here is how to speed your VBA macro up. Move all data into a Variant array. This removes the overhead created by referencing a cell.

Sub HideRows()

    Dim C As Variant
    Dim Data As Variant
    Dim Wks As Worksheet
    
        Set Wks = Worksheets("Summary")
        Data = Wks.Range("b5:b5000").Value
        
        For Each C In Data
            If C = "N/A" Then Wks.Rows(C + 4).EntireRow.Hidden = True
        Next C
    
End Sub

 

[chriscsmith]

I use ozgrid regularly for QA - check this out for speeding up VBA... http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

 

[Wamhoi]

Hi Leith Ross, thanks for the code but I'm getting an error message "Type Mismatch" when at the following point

Wks.Rows(C + 4).EntireRow.Hidden = True

<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1>Also, njimack, I wish I could do that but I have series of non continuous data sets spanning almost 4,000 rows.

Thanks chriscsmith, this is going to help alot!

 

[chriscsmith]

Cool - I know the screen flicker is always an issue when you have large datasets

 

[Leith Ross]

Hello Wamhoi,

Sorry about that. Here is the corrected code.

Sub HideRows()

    Dim C As Variant
    Dim Data As Variant
    Dim R As Long
    Dim Wks As Worksheet
    
        Set Wks = Worksheets("Sheet1")
        Data = Wks.Range("b5:b5000").Value
        R = 5
        
        For Each C In Data
            If C = "N/A" Then Wks.Rows(R).EntireRow.Hidden = True
            R = R + 1
        Next C
    
End Sub

 

[Wamhoi]

Thanks! Its defintely a bit quicker!