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

[tskogstrom]

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

 

[Guest]

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

 

[Guest]

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]

No, sorry. To hide or unhide them one by one make the events take a
minute. Below, I made a Union routine and just two hide/unhide
actions, but that take 20 seconds. To hide- or unhide only a few, take
maybe 1 seconds. I NEED to identify what rows differ - the hidden
before that shouldn't and the visible before, that shouldn't, and just
take action on the specific few rows concerned.

I wrote(Note: I want a routine working on different sheets and
differnet ranges, therfore the Call approach):

Sub Test1()
Call RangeHiddenOrVisible(Range("AE1:AE849"))
End Sub

Sub RangeHiddenOrVisible(rng As Range)
'***********************************
Application.EnableEvents = False
Application.ScreenUpdating = False
Debug.Print "Sub name: RangeHiddenOrVisible: " & rng.Address
Dim rngHid As Range, rngVis As Range
Set rngVis = Nothing
Set rngHid = Nothing

For Each cell In rng
If cell.Value = "1" Then
If rngVis Is Nothing Then
Set rngVis = Cells(cell.Row, cell.Column)
Else
Set rngVis = Union(rngVis, Cells(cell.Row, cell.Column))
End If
Else
If rngHid Is Nothing Then
Set rngHid = Cells(cell.Row, cell.Column)
Else
Set rngHid = Union(rngHid, Cells(cell.Row, cell.Column))
End If
End If
Next cell

Let Blad300.Range("REF_LONS_ROWADDRESS").Value = rngVis.Address
rngHid.Rows.Hidden = True
rngVis.Rows.Hidden = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


Any other suggestions?
Regards,
Tskogstrom

 

[tskogstrom]

I added a intersect check to just union the different rows, but get
error on "Range(Sheet300.Range("a1").value )" - the address string in
the cell dosen't work in the range method. WHY?´?

Kind reagrds
Tskogstrom
-----

Sub Test1()
Call RangeHiddenOrVisible(Range("AE1:AE849"))
End Sub

Sub RangeHiddenOrVisible(rng As Range)
'***********************************
Application.EnableEvents = False
Application.ScreenUpdating = False
Debug.Print "Sub name: RangeHiddenOrVisible: " & rng.Address
Dim rngHid As Range, rngVis As Range
Set rngVis = Nothing
Set rngHid = Nothing

For Each cell In rng
If cell.Value = "1" Then
If Application.Intersect(Cells(cell.Row, cell.Column),
Range(Sheet300.Range("a1").value )) Is Nothing Then GoTo EndNext
If rngVis Is Nothing Then
Set rngVis = Cells(cell.Row, cell.Column)
Else
Set rngVis = Union(rngVis, Cells(cell.Row, cell.Column))
End If
Else
If Not Application.Intersect(Cells(cell.Row, cell.Column),
Range(Sheet300.Range("a1").value )) Is Nothing Then GoTo EndNext
If rngHid Is Nothing Then
Set rngHid = Cells(cell.Row, cell.Column)
Else
Set rngHid = Union(rngHid, Cells(cell.Row, cell.Column))
End If
End If
EndNext:
Next cell

Let Blad300.Range("REF_LONS_ROWADDRESS").Value = rngVis.Address
rngHid.Rows.Hidden = True
rngVis.Rows.Hidden = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub