Fast way to make rows visible and invisible

My application has the following Sub which hides some rows and makes
others visible :

Is there a way to speed up this code somehow so that the cells are set
to visible and invisible in two instructions.

The code is real slow.

Public Sub RegionView(sheet As Worksheet, NavigationColumn As Long)
Dim i As Integer
Dim rowType As String
For i = STARTING_ROW_INDEX To MAXIMUM_ROW_INDEX
rowType = sheet.Cells(i, NavigationColumn).Value
If i < 2 Then
MsgBox rowType
End If
If "br_br" = rowType Then
sheet.Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = True
ElseIf "br_ta" = rowType Then
sheet.Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = True
ElseIf "rg_rg" = rowType Then
sheet.Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = True
ElseIf "rg_tr" = rowType Then
sheet.Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = False
Else
sheet.Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = False
End If
If i > MAXIMUM_ROW_INDEX Then
Exit For
End If
Next i
End Sub

Hi,
Turning off screenupdating should speed things up (you'll still see
your msgbox)
Application.ScreenUpdating = False added at the start is enough for
that (it's automatically turned back on when the code finishes)
Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = True can be reduced
to
Rows(i).EntireRow.Hidden = True which reduces the number of vb
functions used and should therefore speed things up as well.
Ken Johnson

Thanks Ken.

I used your tip.

Hi,
Thanks for the feedback. Hope you're getting reasonable speed now.
ScreenUpdating usually makes a signifcant difference when the code is
changing the active sheet.
Ken Johnson

This turned out to be what I needed ...

Note that there is a limit to about 30 rows to how many rows can be
selected at once using this syntax..

Range(x1:x1,x2:x2...").Select




Public Sub RegionView(sheet As Worksheet, NavigationColumn As Long)
Dim i As Integer, j As Integer
Dim rowType As String, hideRows As String, showRows As String
hideRows = ""
showRows = ""
Dim LastRow As Integer
LastRow = sheet.Cells(rows.Count, "A").End(xlUp).Row
' set all cells to visible
sheet.Cells.EntireRow.Hidden = False

j = 0
For i = STARTING_ROW_INDEX To LastRow
rowType = sheet.Cells(i, NavigationColumn).Value

If "br_branch" = rowType Then
j = j + 1
hideRows = hideRows + CStr(i) + ":" + CStr(i) + ","
ElseIf "br_target" = rowType Then
j = j + 1
hideRows = hideRows + CStr(i) + ":" + CStr(i) + ","
ElseIf "rg_region" = rowType Then
j = j + 1
hideRows = hideRows + CStr(i) + ":" + CStr(i) + ","
End If


' Excel returns a 400 error if more than 30 rows are hidden at a time
If j > 30 Then
hideRows = Left(hideRows, Len(hideRows) - 1)
sheet.Range(hideRows).EntireRow.Hidden = True
j = 0
hideRows = ""
End If

Next i
hideRows = Left(hideRows, Len(hideRows) - 1)
sheet.Range(hideRows).EntireRow.Hidden = True

End Sub