TTom
Well-known Member
- Joined
- Jan 19, 2005
- Messages
- 518
This code is pretty slow if most rows have to be hidden. I'm certain there is a faster way to do this but I'm not seeing it.
I'd appreciate any suggestions.
What am I doing with code:
For each row from 12 to 236 I'm looking to see if there is data in any cell in columns A:F, H, & J. If not, I hide the row.
I'm starting at row 236 and working up to 12 continuing to hide rows until I find data or I reach row 11.
As soon as any row has data or row 11 is reached the the procedure ends leaving all remaining rows visible.
<code>
Sub ReduceDisplayed()
Dim d As Boolean, cc As Byte
Application.ScreenUpdating = False
Range("A236").Activate
'd = False
Do While d = False
If ActiveCell.Row = 11 Then d = True
For cc = 0 To 5
If ActiveCell.Offset(0, cc) <> "" Then d = True
Next cc
If ActiveCell.Offset(7, cc) <> "" Then d = True
If ActiveCell.Offset(9, cc) <> "" Then d = True
If d = False Then
Application.ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(-1, 0).Activate
End If
Loop
Range("A7").Select
Application.ScreenUpdating = True
End Sub
</code>
Thanks, TTom
I'd appreciate any suggestions.
What am I doing with code:
For each row from 12 to 236 I'm looking to see if there is data in any cell in columns A:F, H, & J. If not, I hide the row.
I'm starting at row 236 and working up to 12 continuing to hide rows until I find data or I reach row 11.
As soon as any row has data or row 11 is reached the the procedure ends leaving all remaining rows visible.
<code>
Sub ReduceDisplayed()
Dim d As Boolean, cc As Byte
Application.ScreenUpdating = False
Range("A236").Activate
'd = False
Do While d = False
If ActiveCell.Row = 11 Then d = True
For cc = 0 To 5
If ActiveCell.Offset(0, cc) <> "" Then d = True
Next cc
If ActiveCell.Offset(7, cc) <> "" Then d = True
If ActiveCell.Offset(9, cc) <> "" Then d = True
If d = False Then
Application.ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(-1, 0).Activate
End If
Loop
Range("A7").Select
Application.ScreenUpdating = True
End Sub
</code>
Thanks, TTom