VBA: Filter range by dropdown list selection + another criteria

[Martin_H]

Morning all,

I hope you all had a great weekend.

This newly created thread follows this one.

I wanted to filter the range D4:D590 by value selected from dropdown list located at the cell DK1, which has been solved by Fluff and fadee2. I used this code for it:

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

Now, I would like to add one more criteria.

So basically when I select a value from dropdown list located at cell DK1 it will filter range D4:D590 based on DK1 cell selection and I would like to hide all zero and blank values in the range DO4:DO590 with it.

Thank you.

 

[fadee2]

if by

hide all zero and blank values

you mean you want to further filter the data to omit zero and blank values then try

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
      Range("DO4:DO590").AutoFilter 119, "<> 0", xlAnd, "<>" & ""
   End If
End Sub

hth

 

[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:DO590").AutoFilter 1, Target.Value
      Range("D4:DO590").AutoFilter 112, "<>0", xlAnd, "<>"
   End If
End Sub

 

[Martin_H]

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:DO590").AutoFilter 1, Target.Value
      Range("D4:DO590").AutoFilter 112, "<>0", xlAnd, "<>"
   End If
End Sub

First range should be D4:D590, second range should be DO4:DO590. But still, I am getting a run time error 1004.

      Range("DO3:DO589").AutoFilter 112, "<>0", xlAnd, "<>"

this line is highlighted by yellow.

 

[Fluff]

Remove any filters you have in place & try it again.

 

[Martin_H]

Removed all filters, but still gettin that same error.

 

[Fluff]

Did you change the ranges in the code I provided?

 

[Martin_H]

I have changed ranges to this

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
      Range("DO4:DO590").AutoFilter 112, "<>0", xlAnd, "<>"
   End If
End Sub

 

[Fluff]

In that case change them back to what I suggested. You can only have one autofilter on a sheet unless you are using structured tables.

 

[Martin_H]

I have changed it back, but still same problem occurs.