bhop
10-16-2007, 02:40 PM
From this forum, I successfully deployed the code for autohiding (see below) rows. I have this on 25 different worksheets and when executing the macro on activation of each worksheet, it slows to a crawl.
What are my options for activating the macro to run as needed? Ideally, the cell range for each worksheet could be monitored and then execute the autohide macro only when changed. I am willing to entertain other suggestions as well. Thanks in advance.
VBA for AutoHide:
Private Sub workbook_close()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 101
Const LastRow As Long = 400
'< Set your columns that contain data >
Const FirstCol As String = "C"
Const LastCol As String = "G"
'*****************************
ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
'(we're using columns B to G here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Application.ScreenUpdating = True
End Sub
What are my options for activating the macro to run as needed? Ideally, the cell range for each worksheet could be monitored and then execute the autohide macro only when changed. I am willing to entertain other suggestions as well. Thanks in advance.
VBA for AutoHide:
Private Sub workbook_close()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 101
Const LastRow As Long = 400
'< Set your columns that contain data >
Const FirstCol As String = "C"
Const LastCol As String = "G"
'*****************************
ActiveWindow.DisplayZeros = False
Application.ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
'(we're using columns B to G here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Application.ScreenUpdating = True
End Sub