Faster way to find what part of a range (actually addresses) not intersect with another one?
 

Hi

I got two addresses in sheet cells, it could be : Stored address: $V
$10:$V$25,$V$31:$V$48,$V$54:$V$71 and current sheet result: $V$10:$V
$25,$V$32:$V$48,$V$54:$V$71 .

They tell me what rows are filled with data and I get stored address
from code like Let Range("rowsData") =
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address .
How can I compare these addresses and get the range that is similar.
Above, you see in current range/address that cell/range "V32" is a row
with data content which dosen't the stored has. V32 should then be
Hidden = false - I hide empty rows.


NO LOOP
I want a faster solution than to use "for each .. next" to compare
each cell in one range and test if it intersect with the other range.
That take to long because the addresses use to be much longer and more
complex than the two I wrote above.


With that knowledge, I can just hide/show that particular row/rows
that differ, and that should be much faster than current solution I
got. I want to just run Range("V32").entirerows.hidden = false in this
particular example.
If stored area $V$31:$V$48 instead would have current sheet result $V
$30:$V$48, then i want to make it Hidden= true, of cource.

So, how do I get Range("V32") from these two addresses, where one of
them is a stored address ?

Kind regards
Tskogstrom

Faster way to find what part of a range (actually addresses) not intersect with another one?
 

Try

Sub HideRows()

Dim HideRange As Range
Dim cell As Range

Application.ScreenUpdating = False
For Each cell In Intersect(Range("V:V"), ActiveSheet.UsedRange)
If Intersect(cell, Range(Sheets("Sheet2").Range("A1").Value)) Is Nothing
Then
If HideRange Is Nothing Then
Set HideRange = cell
Else
Set HideRange = Union(HideRange, cell)
End If
End If
Next

If Not HideRange Is Nothing Then
HideRange.EntireRow.Hidden = True
End If
Application.ScreenUpdating = True

End Sub

Regards



"tskogstrom" wrote in message
ups.com...
Hi

I got two addresses in sheet cells, it could be : Stored address: $V
$10:$V$25,$V$31:$V$48,$V$54:$V$71 and current sheet result: $V$10:$V
$25,$V$32:$V$48,$V$54:$V$71 .

They tell me what rows are filled with data and I get stored address
from code like Let Range("rowsData") =
Columns("V:V").SpecialCells(xlCellTypeFormulas, 1).Rows.Address .
How can I compare these addresses and get the range that is similar.
Above, you see in current range/address that cell/range "V32" is a row
with data content which dosen't the stored has. V32 should then be
Hidden = false - I hide empty rows.


NO LOOP
I want a faster solution than to use "for each .. next" to compare
each cell in one range and test if it intersect with the other range.
That take to long because the addresses use to be much longer and more
complex than the two I wrote above.


With that knowledge, I can just hide/show that particular row/rows
that differ, and that should be much faster than current solution I
got. I want to just run Range("V32").entirerows.hidden = false in this
particular example.
If stored area $V$31:$V$48 instead would have current sheet result $V
$30:$V$48, then i want to make it Hidden= true, of cource.

So, how do I get Range("V32") from these two addresses, where one of
them is a stored address ?

Kind regards
Tskogstrom