Row hiding macro, speeding it up?

[TTom]

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

 

[SydneyGeek]

Using a helper column with a worksheet formula can be faster than using Filter, depending upon what is required to be done and upon the worksheet scenario.

Out of interest, how would you make use of Excel's Filter tool to do what the OP requires?

I like the idea of a helper column to simplify the AND / OR criteria, and I've used it a bit myself. It would be mt favoured option here. Once you have the helper column you can use Autofilter or Advanced Filter to display only cells containing a 1. You can chuck the helper in column Z and hide it if you like, but you'll need to put some sort of heading in Z11. All that changes then is the formula that you write in VBA; I perfer to use partial references to simplify the mental gymnastics, so:

=COUNTA(RC1:RC6,RC8,RC10)=0

And filter on FALSE.

EDIT -- Accidental double post: got a posting error first time around. See below...
Denis

 

[SydneyGeek]

Using a helper column with a worksheet formula can be faster than using Filter, depending upon what is required to be done and upon the worksheet scenario.

Out of interest, how would you make use of Excel's Filter tool to do what the OP requires?

I like the idea of a helper column to simplify the AND / OR criteria, and I've used it a bit myself. It would be mt favoured option here. Once you have the helper column you can use Autofilter or Advanced Filter to display only cells containing a 1. You can chuck the helper in column Z and hide it if you like, but you'll need to put some sort of heading in Z11. All that changes then is the formula that you write in VBA; I perfer to use partial references to simplify the mental gymnastics, so:

Sub Macro1()
    
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
    Range("Z12:Z146").FormulaR1C1 = "=COUNTA(RC1:RC6,RC8,RC10)=0"
    Range("Z11:Z146").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("AA11:AA12"), Unique:=False
End Sub

Note: AA11 has the same heading as Z11, and AA12 has FALSE.

BTW, I decided to time both versions. Running each of them 4 times I got the same result every time:

Filter: 1.59999999959837E-02
Hide: 3.10000000026776E-02

So, in this case filtering is faster but the difference between 16 and 31 milliseconds is not a big deal

Denis

 

[Boller]

The 2 macros you timed are not comparable.

To make them comparable :-

Sub Hide()
Dim t
t = Timer
On Error Resume Next
ActiveSheet.ShowAllData
Rows("12:236").EntireRow.Hidden = False
[Z12:Z236].FormulaR1C1 = "=IF(COUNTA(RC1:RC6,RC8,RC10)=0,""d"",1)"
[Z12:Z236].SpecialCells(xlCellTypeFormulas, 2).EntireRow.Hidden = True
On Error GoTo 0
[Z12:Z236].ClearContents
MsgBox Timer - t
End Sub

Sub Filter()
Dim t
t = Timer
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
Rows("12:236").EntireRow.Hidden = False
[Z11:AA11] = "heading"
[AA12] = "FALSE"
Range("Z12:Z236").FormulaR1C1 = "=COUNTA(RC1:RC6,RC8,RC10)=0"
Range("Z11:Z236").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Range("AA11:AA12"), Unique:=False
MsgBox Timer - t
End Sub

I didn't record any difference in the run-times, but I don't like that your Filter method clutters columns Z:AA with headings and formulas.

If that is acceptable, why bother with a macro?
With the Filter in place in column Z which can be set up one time manually, just filter for FALSE as and when required either manually or by a macro.
Of course, the same thing could be achieved by keeping the helper column pemanently without adding a filter and using SpecialCells (manually or by a macro) to hide the relevant rows.
But it's a bit untidy having a permanent helper column, and because of this I think the non-filter method is preferable (merely my personal opinion).

 

[SydneyGeek]

I didn't record any difference in the run-times, but I don't like that your Filter method clutters columns Z:AA with headings and formulas.

Yep. I think the difference is in the creation and deletion of the helper column. But the filter method doesn't have to display the filter data: I usually hide that.

If that is acceptable, why bother with a macro?
With the Filter in place in column Z which can be set up one time manually, just filter for FALSE as and when required either manually or by a macro. Of course, the same thing could be achieved by keeping the helper column pemanently without adding a filter and using SpecialCells (manually or by a macro) to hide the relevant rows.

Like you said, it comes down to personal style. I use the filter method extensively and just provide a button for my users to click.

Under those conditions I have the helper column and the criteria permanently hidden and protected. All I need to do is run the filter.

Bottom line: both methods work, and work fast. :wink:

Denis