In VBA Excel 2003 Iterating in for loop for 800 ro

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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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