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

L

LolaM

New Member
Joined
Sep 7, 2011
Messages
24
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
CountryScotland
LocationGlasgow
Start Date (dd/mm/yyyy)30/04/2013

<tbody>
</tbody>

And on Sheet2, a list of events.
EventCountryLocationStart Date
Event1ScotlandGlasgow01/05/2013
Event2ScotlandGlasgow01/03/2013
Event3ScotlandEdinburgh15/05/2013
Event4EnglandLondon20/05/2013

<tbody>
</tbody>

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!! :biggrin:
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Try this:-
Code: 
[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
 
Upvote 0
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!! ;)
 
Upvote 0
Will do, MickG!! Samples of the two sheets as follows...

Sheet1
ScotlandInclude
EnglandInclude
WalesExclude
Northern IrelandExclude
TypeInternal
Start Date (dd/mm/yyyy)30/04/2013

<tbody>
</tbody>

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
EventCountryTypeStart Date
Event1ScotlandInternal01/05/2013
Event2ScotlandInternal01/03/2013
Event3ScotlandExternal15/05/2013
Event4EnglandInternal20/05/2013

<tbody>
</tbody>

(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!!:biggrin:
 
Upvote 0
Try this:-
Code: 
[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
 
Upvote 0
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.
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA Code to Search and produce results based on the criteria
Replies
0
Views
514
Balajibenz
B
H
  • Question
Count rows based on 2 dates
Replies
1
Views
438
AlanY
A
U
  • Question
VBA: Exclude Data Based on Date
Replies
5
Views
2K
mumps
mumps
U
  • Question
VBA Group Row Based on Cell Critieria
Replies
0
Views
409
unknownymous
U
M
  • Question
VBA to Create Rows based on date differences
Replies
4
Views
936
Fluff
Fluff

Forum statistics

Threads
1,214,950
Messages
6,122,438
Members
449,083
Latest member
Ava19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top