Need macro code based on conditions?

[shushilm]

Hi Team,

Can any one help me with the Macro code for the below situation:

• I have the data validation in B1,B2,B3 where the user will select.
• I have a filter in row5 where I exclude blanks below row5. Example below after filter it will not show row 8 & 11 as its blank.
• Supplier # and Address is pulled based on the condition in B1,B2,B3 from a different sheet in the excel.

What I Need - A Macro which will filter for non blanks every time any of the drop down is changed, so it shows the data based on the current filter condition. Example - I got the below data based on "New Jersey", "Electrical" and "Yes" in B1,B2,B3, if I change Preferred Supplier to "No", the Macro should run to and re filter based on the new condition.

Thank you in advance!!

Rows	Column A	Column B
1	State	New Jersey
2	Scope	Electrical
3	Preferred Supplier (Yes/No)	Yes
4
5	Supplier #	Address
6	ABC1	xyz1
7	ABC2	xyz2
8
9	ABC4	xyz4
10	ABC5	xyz5
11
12	ABC7	xyz7

<tbody>
</tbody>

 

[BarryL]

put this in the sheet object where the data is

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case Is = "$B$1", "$B$2", "$B$3"
Range("A5:B" & Range("B" & Rows.Count).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="***"
End Select
End Sub

 

[shushilm]

Hi Barry,

Thank you so much for the code, it works perfect!!

Have a nice weekend,
Regards,
Shushil

 

[BarryL]

Hi Barry,

Thank you so much for the code, it works perfect!!

Have a nice weekend,
Regards,
Shushil

You're very welcome. You too.