VBA Code to Hide Rows with non matching text from drop down list

[BradM1985]

Hi,

I was hoping for some assistance to create a code for the following purpose.

I have created a drop down list in cell B1 with 24 text options, for ease of assistance, lets say AAA through XXX. I will be importing data from another program into row 6 to 400. I would like to hide all rows excluding those rows that show the same text as the drop down selection in cell B1. So if cell B1 reads 'AAA' I would like all rows between 6 to 400 to be hidden if they do not read exactly 'AAA' If the drop down box was then changed to 'BBB" I would like to only display 'BBB" and hide all other text in row 6 to 400.

I've tried a few options however my skill level is not overly high with VBA. If I could get some assistance that would be highly appreciated.

 

[aRandomHelper]

Must it be VBA? Since you're using 365, you can try the filter function.

Something like:

=FILTER(B6:B400,B6:B400=B1,"")

and put it in column D maybe

 

[BradM1985]

Must it be VBA? Since you're using 365, you can try the filter function.

Something like:

=FILTER(B6:B400,B6:B400=B1,"")

and put it in column D maybe

Preference would be to use VBA, although its not entirely mandatory. Filter function (no idea it existed) will work if from what I've trialed. thankyou .

 

[Peter_SSs]

Welcome to the MrExcel board!

Preference would be to use VBA

You haven't said what column the imported "AAA" etc values would be in. I have assumed column A but you should be able to see what to alter if it is a different column.
Post back if you do need further help with that, or how to implement this Worksheet_Change event code.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B1")) Is Nothing Then
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    If Len(Range("B1").Value) > 0 Then Range("A5", Range("A" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=Range("B1").Value
  End If
End Sub