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

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

Application.EnableEvents = False
Application.ScreenUpdating = False

HTH,
Bernie
MS Excel MVP


"Sabine" wrote in message
...
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

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

Thanks!

Sabine


"Bernie Deitrick" <deitbe @ consumer dot org schreef in bericht
...
Try turning off Events and screen updating prior to hiding the rows:

Application.EnableEvents = False
Application.ScreenUpdating = False

HTH,
Bernie
MS Excel MVP


"Sabine" wrote in message
...
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

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

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

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

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