VBA: Filter range by dropdown list selection

[Martin_H]

Morning all,

I want to filter the range "D4:D590" by value selected from dropdown list located @ the cell "DK1".

So basically when I select value "apples" from dropdown list @ cell "DK1" I want to see only rows with value "apples" in range "D4:D590".

Thank you.

 

[fadee2]

hi,

try

sub frange()
myvalue = range("DK1").value
    With ActiveSheet
        Range("A:D").AutoFilter Field:=4, Criteria1:=myvalue
    End With
end sub

 

[Martin_H]

Thank you for your reply.

I am getting 1004 error. Application-defined or object-defined error.

 

[fadee2]

try

sub frange()
dim myvalue as string
myvalue = Activesheet.range("DK1").value
    With ActiveSheet
        Range("A:D").AutoFilter Field:=4, Criteria1:=myvalue
    End With
end sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "DK1" Then
      Range("D4:D590").AutoFilter 1, Target.Value
   End If
End Sub

This needs to go in the relevant sheet module & will change the filter whenever DK1 is changed.

 

[Martin_H]

Both ideas working great. Thank you for your time guys. I appreciate it.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[fadee2]

???