This event code runs and works but it is extremely slow. I found it at http://www.vbaexpress.com/kb/getarticle.php?kb_id=512 It was only tested with Excel 2000 and 2003 but it does exactly what I needed so I tried it in 2010. I'm hoping someone can help to speed it up by making it more compatible with 2010 or just replace it completely with something known to work in 2010 to accomplish the same but isn't slow.
I downloaded the example file and ran it (in 2010 as .xls macro enabled) and it works very fast, almost immediately for the 16 rows within it's range. I'm using it for 180 rows. Still, it doesn't seem like it should take as long as it does, around two minutes for my additional rows.
Basically, I need the code to only check the cells in A1:A180 and hide all rows where the cell in column A is blank. Those that are not blank will always have a 1 in them. It has to be a sheet event and needs to be fast since the sheet may be selected often.
Thanks for any help or suggestions you can provide!Option Explicit Private Sub Worksheet_Activate() Dim HiddenRow&, RowRange As Range, RowRangeValue& '***************************** '< Set the 1st & last rows to be hidden > Const FirstRow As Long = 1 Const LastRow As Long = 180 '< Set the columns that may contain data > Const FirstCol As String = "A" Const LastCol As String = "T" '***************************** ActiveWindow.DisplayZeros = False Application.ScreenUpdating = False For HiddenRow = FirstRow To LastRow '(we're using columns A to T 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
Bookmarks