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


"sabine" wrote in message
...
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" <deitbe @ consumer dot org schreef in bericht
...
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" wrote in message
...
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

"Barb Reinhardt" schreef in bericht
...
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" wrote:

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