Excel 2003 VBA - Help with hiding rows based on three conditions on two different worksheets

[LolaM]

Help!! I have a rather complex (to me) scenario which I just cannot work out the best way to deal with this... any suggestions or samples would be really appreciated!!

I have a workbook with two worksheets, Sheet1 and Sheet 2. Based on the data entered in the 2nd column on Sheet1, I'd like a macro which hides rows for Events which do not meet the criteria entered on Sheet1.

So in the example below, on Sheet2, I only want to see rows where the Country=Scotland, AND the Location=Glasgow, AND the Start Date is greater than (or equal) the Start Date on Sheet1.

Sample of Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Scotland[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]Glasgow[/TD]
[/TR]
[TR]
[TD]Start Date (dd/mm/yyyy)[/TD]
[TD]30/04/2013[/TD]
[/TR]
</tbody>[/TABLE]

And on Sheet2, a list of events.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Event[/TD]
[TD]Country[/TD]
[TD]Location[/TD]
[TD]Start Date[/TD]
[/TR]
[TR]
[TD]Event1[/TD]
[TD]Scotland[/TD]
[TD]Glasgow[/TD]
[TD]01/05/2013[/TD]
[/TR]
[TR]
[TD]Event2[/TD]
[TD]Scotland[/TD]
[TD]Glasgow[/TD]
[TD]01/03/2013[/TD]
[/TR]
[TR]
[TD]Event3[/TD]
[TD]Scotland[/TD]
[TD]Edinburgh[/TD]
[TD]15/05/2013[/TD]
[/TR]
[TR]
[TD]Event4[/TD]
[TD]England[/TD]
[TD]London[/TD]
[TD]20/05/2013[/TD]
[/TR]
</tbody>[/TABLE]

So in this scenario, only the FIRST row meets the 3 criteria (Scotland, Glasgow, date after 30th April), thus all the other rows should be hidden.

I can do all these individually - just having problems doing them all together at once!!

Thanks in advance for pointing me in the right direction!!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG14Jan11
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dt          [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] Temp        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    Temp = Range("B1") & Range("B2")
    Dt = .Range("B3")
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Dim[/COLOR] Twn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    .Cells.EntireRow.Hidden = False
    [COLOR="Navy"]Set[/COLOR] Rng = .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] Rng
    [COLOR="Navy"]If[/COLOR] CDbl(DateValue(Dn(, 3))) < CDbl(DateValue(Dt)) [COLOR="Navy"]Then[/COLOR] Dn.Rows.Hidden = True
    [COLOR="Navy"]If[/COLOR] Not Dn & Dn(, 2) = Temp [COLOR="Navy"]Then[/COLOR] Dn.Rows.Hidden = True
[COLOR="Navy"]Next[/COLOR] Dn
MsgBox "Run"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[LolaM]

Thanks MickG! You're my knight in shining armour on this stuff!!

Thing is, on reviewing this with my boss... he's changed how he wants it to work.

On Sheet1, there'll now be 4 Countries listed (Scotland, England, Wales, Northern Ireland), with a data validation list of either 'Include' or 'Exclude' beside each of them. The user will set each to include/exclude appropriately. It could be only one included, or all 4, or a subset.

Location will be replaced with Type (the Type of Event, which will be again selected from a data validation list - there are 2 options, Internal and External).

Start Date will be the same.

What I'd now like to happen is, for each row on Sheet2, if...
Country set to Include on Sheet1 matches Country on that row
AND
If Type is a match to Sheet1
AND
If Event Start Date is greater than or equal to the Start Date on Sheet1, then show that row. Otherwise hide it.

Sorry to be such a nuisance - hoping you can help again!!

 

[MickG]

Please show new layout of each sheet.
Regrds Mick

 

[LolaM]

Will do, MickG!! Samples of the two sheets as follows...

Sheet1
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Scotland[/TD]
[TD]Include[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Include[/TD]
[/TR]
[TR]
[TD]Wales[/TD]
[TD]Exclude[/TD]
[/TR]
[TR]
[TD]Northern Ireland[/TD]
[TD]Exclude[/TD]
[/TR]
[TR]
[TD]Type[/TD]
[TD]Internal[/TD]
[/TR]
[TR]
[TD]Start Date (dd/mm/yyyy)[/TD]
[TD]30/04/2013[/TD]
[/TR]
</tbody>[/TABLE]

Note that for all 4 Countries, the user can select either 'Include' or 'Exclude' using a dropdown data validation list. For the Type, the user has the choice of 'Internal' or 'External', also from a dropdown data validation list.

Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Event[/TD]
[TD]Country[/TD]
[TD]Type[/TD]
[TD]Start Date[/TD]
[/TR]
[TR]
[TD]Event1[/TD]
[TD]Scotland[/TD]
[TD]Internal[/TD]
[TD]01/05/2013[/TD]
[/TR]
[TR]
[TD]Event2[/TD]
[TD]Scotland[/TD]
[TD]Internal[/TD]
[TD]01/03/2013[/TD]
[/TR]
[TR]
[TD]Event3[/TD]
[TD]Scotland[/TD]
[TD]External[/TD]
[TD]15/05/2013[/TD]
[/TR]
[TR]
[TD]Event4[/TD]
[TD]England[/TD]
[TD]Internal[/TD]
[TD]20/05/2013[/TD]
[/TR]
</tbody>[/TABLE]

(Note, there can be up to 40 or so events listed on Sheet2)

In this scenario, if (on Sheet1) the user selects 'Include' for both Scotland and England, Selects the Type as Internal, and enters the Start Date as 30/04/2013, then only Events 1 and 4 will be shown on Sheet2.

Thanks again in advance!!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG16Jan58
[COLOR="Navy"]Dim[/COLOR] Rng1        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn1         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn2         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng2        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Typ         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range("A1:A4")
         Typ = .Range("B5")
          Dt = .Range("B6")
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    .Cells.Rows.Hidden = False
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
    Rng2.EntireRow.Hidden = True
[COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn2 [COLOR="Navy"]In[/COLOR] Rng2
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn1 [COLOR="Navy"]In[/COLOR] Rng1
        [COLOR="Navy"]If[/COLOR] CDbl(DateValue(Dn2(, 3))) >= CDbl(DateValue(Dt)) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] Dn1 = Dn2 And Dn1(, 2) = "Include" And Dn2.Offset(, 1) = Typ [COLOR="Navy"]Then[/COLOR]
                    Dn2.Rows.Hidden = False
                    [COLOR="Navy"]Exit[/COLOR] For
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn1
    [COLOR="Navy"]Next[/COLOR] Dn2
MsgBox "Run"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

This is another option , Using the Worksheet Change Event.
Any Change in Range ("B1:B6") on sheet (1) will run the code to hide the appropriate rows.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng1        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn1         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn2         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng2        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Typ         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Sheets("Sheet1").Range("B1:B6")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range("A1:A4")
         Typ = .Range("B5")
          Dt = .Range("B6")
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    .Cells.Rows.Hidden = False
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
    Rng2.EntireRow.Hidden = True
[COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn2 [COLOR="Navy"]In[/COLOR] Rng2
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn1 [COLOR="Navy"]In[/COLOR] Rng1
        [COLOR="Navy"]If[/COLOR] CDbl(DateValue(Dn2(, 3))) >= CDbl(DateValue(Dt)) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] Dn1 = Dn2 And Dn1(, 2) = "Include" And Dn2.Offset(, 1) = Typ [COLOR="Navy"]Then[/COLOR]
                    Dn2.Rows.Hidden = False
                    [COLOR="Navy"]Exit[/COLOR] For
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn1
    [COLOR="Navy"]Next[/COLOR] Dn2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick