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

 

[Gates Is Antichrist]

As I see that you're familiar with .Offset, for one improvement just offset from the initial range (cell) rather than changing activecell. IOW use something like
with Range("A236")
and fast integer offsets from that, without messing with activecell at all.

 

[Boller]

[A:A].Insert
[A11:A236].EntireRow.Hidden = False
[A11:A236].FormulaR1C1 = "=IF(COUNTA(RC[2]:RC[6],RC[8],RC[10])=0,""d"",1)"
On Error Resume Next
[A11:A236].SpecialCells(xlCellTypeFormulas, 2).EntireRow.Hidden = True
On Error GoTo 0
[A:A].Delete

 

[TTom]

Boller:
After a slight tweak to my liking, this code works great!
And I learned a new technique.
Thanks TTom

 

[SydneyGeek]

Hi TTom,

The filter is definitely faster than looping. You can speed it up further by setting calculation to Manual before running the filter, then reinstating Automatic afterwards. Reason: filtering or redisplaying cells triggers calculation. So, before the filter:

Application.Calculation = xlManual

after the filter:
Application.Calculation = xlAutomatic

Denis

 

[TTom]

Thanks Dennis.
I've done some filtering, but will need to read a bit more to learn how to accomplish my specific goal as described.
Any suggested reading, especially when combined with vba?
TTom

 

[Boller]

The filter is definitely faster than looping

There is no specific method for doing this sort of thing that is always faster than all other methods.

It depends upon the particular worksheet scenario.

Making use of Filter is not necessarily the fastest method, but often it is.

 

[SydneyGeek]

TTom:
MrExcel's VBA book is a good source for VBA code covering a whole range of stuff.

Boiler:

There is no specific method for doing this sort of thing that is always faster than all other methods.

It depends upon the particular worksheet scenario.

True, but if you can operate on cells/rows in bulk, it is usually preferable to looping through the data one cell at a time.
Actually, I was sloppy with my terminology. Because you are hiding rows I used the term "filter", which is in effect what you are doing, even if you don't specifically apply one of the filter methods.
And in a large workbook, setting calculation to Manual can make a significant difference when you show or hide cells.

Edited to clarify...

Denis

 

[Boller]

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?
(Presumably, filter each of the 9 relevant columns one by one for blanks, select the visible cells via SpecialCells, remove the filter(or select ShowAll), then hide the selected rows.)

 

[TTom]

Thanks for all of your input.
I always find it interesting the different methods to apply to the same problem.

I use a Filter in a Ledger Sheet I have in same Workbook, but for a slightly different purpose it's seems well suited for.
As for the issue at hand...
The 'Boller' method seems to be quick enough for my needs (<1second) and does exactly what I need with ability to tweak.

I'm always learning, and always grateful for insights, feedback, and assistance...
TTom