Hide rows based on the value selected under the data validation field

[bokishai]

Hi,


I have a excel sheet showing the agent sales by country.


I have used the data validation filter as "Country" and the below details are the agents & the sales figures. So based on the country selection the agents values are populated.


However, the agents are unique for each country and when the filter is used, i need to scroll down to check the sale figures. this is because the below agent listing is irrespective of the country it belongs.


I want to add a macro to hide the rows that are blank or zero or in simple words which do not belong to that country.


I will really appreciate your assistance in this regard.


Thanks,

Sachin

 

[Faseeh]

HI bokishai!!


Have to you tried goggling the Forum?? Here is the one i found most relavent:


http://chandoo.org/forums/topic/hiding-and-unhiding-rows-using-vba-code


Regards,

Faseeh

 

[mercatog]

Hi, here file and code using Autofilter. (code using then event Change of the worksheet)

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$3" Then
With ActiveSheet
.AutoFilterMode = False
If Target.Value <> "" Then .Range("B5:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).AutoFilter Field:=1, Criteria1:=Target.Value
End With
End If
End Sub

[/pre]

Here link to file: http://speedy.sh/hY7R6/UsingFilter.xlsm


regards