PDA

View Full Version : Need help with macro to only unhide 5 rows at a time based on cell value



tbuys42
06-14-2007, 07:52 PM
I am new to VBA and macros...I have written a macro to unhide rows in a form when the cell value = 1, but I would like to limit it to only unhiding 5 rows at a time to speed up processing time (it would be rare to need more than 5 rows at a time for this form).

I am using a For Each...Next Loop in my current code, but am running stuck. Any help would be appreciated.

mikerickson
06-14-2007, 08:38 PM
If you post the code, it would be easier to see whats going on.

tbuys42
06-14-2007, 08:55 PM
Here's the code:

Sub UnProcessData()
Dim oRow As Range

With ActiveSheet

For Each oRow In .UsedRange.Rows
If .Cells(oRow.Row, "J").Value = 1 Then
oRow.EntireRow.Hidden = False
Else
oRow.EntireRow.Hidden = False
End If
Next oRow

End With

End Sub

mikerickson
06-14-2007, 09:17 PM
The code you posted unhides all the rows in .UsedRange. That didn't sound like what you described so, I modified it so that only those rows with a 1 in column J are unhidden. Otherwise the row's visiblity is unchanged.
This also sets the screenupdating to False while the routine runs. That makes the code run faster and avoids screen flicker.

A counter was added, as soon as five rows are unhidden, the routine stops.


Sub UnProcessData()
Dim oRow As Range
Dim counter As Integer

Application.ScreenUpdating = False

With ActiveSheet

For Each oRow In .UsedRange.Rows
If .Cells(oRow.Row, "J").Value = 1 Then
oRow.EntireRow.Hidden = False
counter = counter + 1
If counter = 5 Then Exit For
End If
Next oRow

End With

Application.ScreenUpdating = True
End Sub

tbuys42
06-15-2007, 04:17 AM
Thanks, That helps alot!!