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


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Rows[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]State[/TD]
[TD]New Jersey[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Scope[/TD]
[TD]Electrical[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Preferred Supplier (Yes/No)[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Supplier #[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ABC1[/TD]
[TD]xyz1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]ABC2[/TD]
[TD]xyz2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]ABC4[/TD]
[TD]xyz4[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]ABC5[/TD]
[TD]xyz5[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]ABC7[/TD]
[TD]xyz7[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.