Speed up vba macro

Wamhoi

New Member
Joined
Mar 4, 2011
Messages
48
So, I'm trying to hide rows based on an N/A value and wrote the following code.

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?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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.
Code:
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
 
Upvote 0
Hi Leith Ross, thanks for the code but I'm getting an error message "Type Mismatch" when at the following point
Code:
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!
 
Upvote 0
Hello Wamhoi,

Sorry about that. Here is the corrected code.
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top