Excel 2003 - Hiding rows based on conditions on two worksheets

[LolaM]

OK, yet another one I'm struggling to work out myself and could do with some help on!!

I have two worksheets (Sheet1 and Sheet2). Based on meeting a condition on Sheet1 AND Sheet2, I'd like to use macros to automatically hide rows on Sheet2.

Sheet1 sample:

Type	Selection
A	Include
B	Exclude
C	Exclude
D	Exclude
E	Exclude

<tbody>
</tbody>

Sheet2 sample:

Link	Type
Sample URL1	A
Sample URL2	A
Sample URL3	B
Sample URL4	C
Sample URL5	C
Sample URL6	D
Sample URL7	D
Sample URL8	E
Sample URL9	E
Sample URL10	E

<tbody>
</tbody>

The conditions are:
If on Sheet1, a Type has the Selection "Include", then any rows on Sheet2 which have that same Type are shown (ie. not hidden).

If on Sheet1, a Type has the Selection "Exclude",then any rows on Sheet2 which have that same Type are hidden.

So in the example tables above, if ONLY Type A = Include, then on Sheet2, the rows for Sample URL1 and Sample URL2 are shown, and the others are hidden.

Also in the example tables above, if Type A = Include AND Type B = Include (and all other Types are Exclude), then on Sheet2, the rows for Sample URL1, Sample URL2 AND Sample URL3 are shown, and the others are hidden.

I'm fine with the concept of If... And... on the same worksheet, it's trying to get it to look at two separate worksheets I'm struggling with!!

I'm hoping this explanation of what I'm trying to do makes sense! Not sure how to attach a sample XLS on here...

Thanks in advance!!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG04Jan16
[COLOR="Navy"]Dim[/COLOR] Rng1        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng2        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng1
       [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1) = "Exclude" [COLOR="Navy"]Then[/COLOR]
            .Item(Dn.Value) = Empty
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng2
    [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Hidden = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[LolaM]

Brilliant, that worked a treat!! Thanks MickG!!!

Although... I do have another question now, sorry... :D

How would I do the same, but comparing the same range on Sheet2 to a single cell on Sheet1??

Thanks in advance again!! :D

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG11Jan20
[COLOR="Navy"]Dim[/COLOR] Temp      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Temp = Sheets("Sheet1").Range("A1")
    
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    '[COLOR="Green"][B]Use the line below if you want to compare column "B"[/B][/COLOR]
    '[COLOR="Green"][B]Set Rng = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn = Temp [COLOR="Navy"]Then[/COLOR] Dn.Rows.Hidden = True
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick