How to make a few rows visible and hide some others ... faster
 

Here's the case:

STR1 -
In a cell A1 in a hidden sheet, Sheet300, I got the string (str1):
$V$8:$V$25,$V$31:$V$48,$V$54:$V$71,$V$77:$V$94,$V$ 100:$V$117,$V$123:$V
$140,$V$142:$V$233,$V$238:$V$292
Str1 tells us the visible rows before worksheet_change or
worksheet_activate event. (This comes from a "Let
Sheet300.Range("a1").value =
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address" line
in another event)

STR2
I get another string (str2) from activesheet:
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address

Str2 tells us the visible rows we want to have, within the
worksheet_change or worksheet_activate.
(Columns("V:V") contain cells with boolean "FALSE" to be visible rows
and a long/integer "1" to be a hidden row.)


I need to compare the ranges these two represent and hide the rows
that shouldn't be visible and show the rows that should.

Today I use:
'Check with stored #rows - if visible rows should be changed
If Sheet300.Range("A1").Value <
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address Then
'Show all rows -------
Application.EnableEvents = False
Range("A1:A250").EntireRow.Hidden = False
'Show correct number of rows as Helpcells give
Columns("V:V").SpecialCells(xlCellTypeFormulas,
1).EntireRow.Hidden = True
End If

This is however much to slow, taking at least 5 seconds. I need a
faster way. I guess I just want two ranges ow rows, one to hide and
one to make visible.

Often, it is just one or a few rows that differ, to be hide and to be
visible.
Can anybody tell me how to do this quicker for the user? I'm happy to
all sugestions.

Kind regards
Tskogstrom

How to make a few rows visible and hide some others ... faster
 

Yes, it seems unnecessary to do all that. I don't use "SpecialCells" often
enough to know if that is the source of the slowdown. I would think all you
have to do is loop through each row and hide it or not depending on Col("V")
of sheet 300 being a "1"

Dim Cell As Range

For Each Cell In Range("A1:A250")
Rows(Cell.Row).Hidden = Sheet300.Cells("V" & Cell.Row) = "1"
Next Cell


"tskogstrom" wrote:

Here's the case:

STR1 -
In a cell A1 in a hidden sheet, Sheet300, I got the string (str1):
$V$8:$V$25,$V$31:$V$48,$V$54:$V$71,$V$77:$V$94,$V$ 100:$V$117,$V$123:$V
$140,$V$142:$V$233,$V$238:$V$292
Str1 tells us the visible rows before worksheet_change or
worksheet_activate event. (This comes from a "Let
Sheet300.Range("a1").value =
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address" line
in another event)

STR2
I get another string (str2) from activesheet:
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address

Str2 tells us the visible rows we want to have, within the
worksheet_change or worksheet_activate.
(Columns("V:V") contain cells with boolean "FALSE" to be visible rows
and a long/integer "1" to be a hidden row.)


I need to compare the ranges these two represent and hide the rows
that shouldn't be visible and show the rows that should.

Today I use:
'Check with stored #rows - if visible rows should be changed
If Sheet300.Range("A1").Value <
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address Then
'Show all rows -------
Application.EnableEvents = False
Range("A1:A250").EntireRow.Hidden = False
'Show correct number of rows as Helpcells give
Columns("V:V").SpecialCells(xlCellTypeFormulas,
1).EntireRow.Hidden = True
End If

This is however much to slow, taking at least 5 seconds. I need a
faster way. I guess I just want two ranges ow rows, one to hide and
one to make visible.

Often, it is just one or a few rows that differ, to be hide and to be
visible.
Can anybody tell me how to do this quicker for the user? I'm happy to
all sugestions.

Kind regards
Tskogstrom

How to make a few rows visible and hide some others ... faster
 

Sorry, change "Sheet300.Cell" to "Sheet300.Range"

Dim Cell As Range

For Each Cell In Range("A1:A250")
Rows(Cell.Row).Hidden = Sheet300.Range("V" & Cell.Row) = "1"
Next Cell


"tskogstrom" wrote:

Here's the case:

STR1 -
In a cell A1 in a hidden sheet, Sheet300, I got the string (str1):
$V$8:$V$25,$V$31:$V$48,$V$54:$V$71,$V$77:$V$94,$V$ 100:$V$117,$V$123:$V
$140,$V$142:$V$233,$V$238:$V$292
Str1 tells us the visible rows before worksheet_change or
worksheet_activate event. (This comes from a "Let
Sheet300.Range("a1").value =
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address" line
in another event)

STR2
I get another string (str2) from activesheet:
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address

Str2 tells us the visible rows we want to have, within the
worksheet_change or worksheet_activate.
(Columns("V:V") contain cells with boolean "FALSE" to be visible rows
and a long/integer "1" to be a hidden row.)


I need to compare the ranges these two represent and hide the rows
that shouldn't be visible and show the rows that should.

Today I use:
'Check with stored #rows - if visible rows should be changed
If Sheet300.Range("A1").Value <
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address Then
'Show all rows -------
Application.EnableEvents = False
Range("A1:A250").EntireRow.Hidden = False
'Show correct number of rows as Helpcells give
Columns("V:V").SpecialCells(xlCellTypeFormulas,
1).EntireRow.Hidden = True
End If

This is however much to slow, taking at least 5 seconds. I need a
faster way. I guess I just want two ranges ow rows, one to hide and
one to make visible.

Often, it is just one or a few rows that differ, to be hide and to be
visible.
Can anybody tell me how to do this quicker for the user? I'm happy to
all sugestions.

Kind regards
Tskogstrom