Faster code to quickly hide several rows?

[Sabine]

Hallo everybody,

I have a very powerful spreadsheet, with lots of formulas and activeX
elements / codes / names. the size is about 7 MB. Sofar no problem, but now
I want to hide certain rows which cells in a certain kolom are marked as
TRUE (as a result from a choice made by ActiveX Option buttons). When I klik
on a Optionbutton, the macro should hide those rows 40 separate ranges, all
in the same column, each range 12 by. So far I have made herefore only one
range: verbergenlijst =input!$AW$163:$AW$3499, containing rows which would
not needed to be checked, which makes the

The code I figured out myself does work, but is quite slow on this sheet,
even if I set calculation on manual just before hiding. the hiding of max
40*12 rows takes about 17 seconds, the unhiding takes about 51 seconds.

I am looking for a better code than below:

Sub hiding()
Set r = Range("verbergen_lijst")

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

For n = 1 To r.Rows.Count
If r.Cells(n, 1) = True Then r.Cells(n, 1).EntireRow.Hidden = True Else
r.Cells(n, 1).EntireRow.Hidden = False
Next n

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

End Sub

Who could show me a better / faster code for hiding/unhiding the 40 * 12
rows if containing 'TRUE' in a certain kolom(cel)?

Thanks in advance,

Sabine

 

[Bernie Deitrick]

Try turning off Events and screen updating prior to hiding the rows:

Application.EnableEvents = False
Application.ScreenUpdating = False

HTH,
Bernie
MS Excel MVP

 

[Barb Reinhardt]

See code below. I've found that if you define one larger range to hide and
then hide it all at once, execution speeds up a bit.

Sub hiding()
Dim r As Excel.range
Dim RowHidden As Excel.range
Dim n As Long

Set r = range("verbergen_lijst")

With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

r.EntireRow.Hidden = False

Set RowHidden = Nothing
For n = 1 To r.Rows.Count
If r.Cells(n, 1) = "True" Then '<~~changed from Boolean True to "True"
as entry
If RowHidden = Nothing Then
Set RowHidden = r.Cells(n, 1)
Else
Set RowHidden = Union(RowHidden, r.Cells(n, 1))
End If
End If
Next n

If Not RowHidden = Nothing Then
RowHidden.EntireRow.Hidden = True
End If

With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

End Sub

HTH,
Barb Reinhardt

 

[Sabine]

Thanks!

Sabine

Try turning off Events and screen updating prior to hiding the rows:

Application.EnableEvents = False
Application.ScreenUpdating = False

HTH,
Bernie
MS Excel MVP

 

[Sabine]

Dear Barb,

sorry for my late reaction, I am fighting with hardware problems, therefore
no sooner reaction yet.
And thank you very much for your code. I just tried to run it, but come
across 2 problems:
the line
as entry
causes a problem, since VBA doesn't know this command. I marked it as a
remark (ignoring it), but then I get another message (in Dutch, translated
into): Compilation error: invalid use of object, caused by the word Nothing
in the line.
If RowHidden = Nothing Then
Unfortunately I don't know how to replace the Nothing. Could you please try
out the code on your own speadsheet?

24 hours from now I will have another PC to work on (now I am forced into
VGA mode which is crap on Windows), so I can go on testing this code.....

Once again, thanks in advance for having a further look at my problem!

Sabine

 

[Bernie Deitrick]

Sabine,

It should be

If RowHidden Is Nothing Then

I haven't timed row hiding specifically, but row deletion using the manner
described is still much slower than grouping the rows prior to deletion.
Another approach would be to sort the rows based on your criteria, and hide
the rows as a block, or to use data filters (though you can only use it on
one table per sheet).

Bernie

 

[sabine]

Dear Bernie,

Finally settled on my backup-laptop and ready to go on testing....

Thank you VERY much for your help, I changed the = to is, and I could test
the code now. It does help a little bit, the hiding of rows takes now 12
seconds instead of 14, and the unhiding takes now 34 seconds instead of 38
seconds.( I am stuk with 40 ranges, each range containing 12 cells, not
adjacent, so the number of rows to be hidden varies between 0 * 40 and 12 *
40, the overal range is input!$AW$163:$AW$3499, consisting of 3336 rows) I
I am so glad that you used the UNION code, which I discovered but could not
implement, since I did't use the right type of variable (Excel.Range). I
will keep the new code .

Unfortunately, sorting the rows is NO option, using data filters is not
possible, too.

Thanks again,

Sabine

 

[Bernie Deitrick]

Sabine,

Since all your values are in one column, you could try using just one filter:

Sub HideRows()
Range("verbergen_lijst").AutoFilter Field:=1, Criteria1:="<>true"
End Sub

And then to show the hidden rows:

Sub UundoFilter()
Range("verbergen_lijst").Parent.ShowAllData
End Sub

HTH,
Bernie
MS Excel MVP