Row hiding macro, speeding it up?

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. :confused:
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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

Banned
Joined
Apr 11, 2006
Messages
2,328
Code:
[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

Well-known Member
Joined
Jan 19, 2005
Messages
518
Boller:
After a slight tweak to my liking, this code works great!
And I learned a new technique.
Thanks :biggrin: TTom
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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

Well-known Member
Joined
Jan 19, 2005
Messages
518
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

Banned
Joined
Apr 11, 2006
Messages
2,328

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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

Well-known Member
Joined
Jan 19, 2005
Messages
518
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 :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,862
Members
414,409
Latest member
FloordAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top