In VBA Excel 2003 Iterating in for loop for 800 rows taking too much time to get the desired output?How to overcome this?

Question

Please suggest the solution to iterate 800 rows in Excel 2003 for hiding and unhiding the rows.

For i = 0 To WSTreatmentOutComes.Range("F14:F813").Rows.Count - 1

                If WSTreatmentOutComes.Range("F" & 14 + i).Value = "" Or WSTreatmentOutComes.Range("F" & 14 + i).Value = "0" Then
                    For j = 0 To 9
                        WSTreatmentOutComes.Range("F" & 14 + i + j).Rows.Hidden = True
                    Next j
                Else
                    For k = 0 To 9
                        If WSTreatmentOutComes.Range("G" & 14 + i + k).Value = "" Or WSTreatmentOutComes.Range("G" & 14 + i + k).Value = "0" Then
                        WSTreatmentOutComes.Range("F" & 14 + i + k).Rows.Hidden = True
                        End If
                    Next k
                End If
                i = i + 9
            Next i

I want to iterate through 800 rows in vba excel 2003 and based on if condition I want to hide or unhide that row. — Vishal I P, Apr 11 '13 at 12:57
you can do it without a loop if you want, that will be significantly faster. — Our Man in Bananas, Apr 11 '13 at 13:15
so you loop 800 times, adding 9 to i in your loop? Then inside your loop you check the next 9 rows before decising to hide? — Our Man in Bananas, Apr 11 '13 at 13:17
see my answer below to do it without a loop and hide all the rows that match the test in one step — Our Man in Bananas, Apr 11 '13 at 13:27

score 4 · Accepted Answer · edited May 23 '17 at 11:43

in column A put a formula test like this:

Range("A14:A813").formula="=IF(AND(F:F="""",G:G=0),NA(),"""")"

then use code like this to hide or unhide:

' first, unhide all rows:

WSTreatmentOutComes.rows.hidden=false
'
'now hide the ones where there is an NA# error from the formula:
WSTreatmentOutComes.Range("A14:A813").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True

finally, clean up column A:

Range("A14:A813").clearcontents

there you go, hides them instantly

more on this method/technique at StackOverFlow: how-to-delete-multiple-rows-without-a-loop-in-excel-vba

score 1 · Answer 2 · answered Apr 11 '13 at 13:11

It seems you should be able to get rid of the inner loop by just hiding the block all in one go. Something close to this should work:

WSTreatmentOutComes.Range("F" & 14 + i & ":F" & 14 + i + 9).Rows.Hidden = True

also, without seeing the start of your code, are you turning off ScreenUpdating at the start?

Application.ScreenUpdating = False
<your block of code here>
Application.ScreenUpdating = True

score 1 · Answer 3 · answered Apr 11 '13 at 14:15

This is how I would do it:

Option Explicit

Public Sub MacroHider()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim i As Long, j As Long, k As Long
    Dim rng As Range, vals() As Variant
    Set rng = WSTreatmentOutComes.Range("F14:F813")
    'get all of the data in the range'
    ReDim vals(1 To 800, 1 To 1)
    vals = rng

    For i = 1 To UBound(vals, 1) Step 10

        If vals(i) = "" Or vals(i) = "0" Then
            For j = 0 To 9
                rng.Rows(i + j).Hidden = True
            Next j
        Else
            For k = 1 To 9  '(already checked the first row)'
                If vals(i + k) = "" Or vals(i + k) = "0" Then
                    rng.Rows(i + k).Hidden = True
                End If
            Next k
        End If

    Next i

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

score 0 · Answer 4 · edited Jul 05 '18 at 15:46

0

With this code I hide the rows where the A column is "1":

Sub Test()
    For i = 1 To Sheet1.Range("A1:A50").Rows.Count    
        If Sheet1.Range("A" & i).Value = "1" Then
            Sheet1.Range("A" & i).Rows.Hidden = True
        End If
    Next
End Sub

So if you want to hide rows if a certain condition in that row is met, you just have to adapt the If-condition and the range.

edited Jul 05 '18 at 15:46

Our Man in Bananas

5,809
21
91
148

answered Apr 11 '13 at 13:01

Coder14

1,305
1
9
26

For loop works fine if there are only 50 to 100 rows.If there are more than 500 to 1000 rows it is taking to much time. In my case it is nested for loop which is again slow. – Vishal I P Apr 11 '13 at 13:11

In VBA Excel 2003 Iterating in for loop for 800 rows taking too much time to get the desired output?How to overcome this?

4 Answers4

Linked