0

I'm trying to create a function that inserts a filter within a cell range if a cell contains a value

Here is my code:

Sub FilterFunc()
  Dim i As Long, lastCol As Long
  Dim rng As Range, cell As Range
  Dim wSheet As Worksheet

  Set wSheet = Worksheets("Sheet1") 
  'find the last column in row one
  lastCol = wSheet.Cells(1, Columns.Count).End(xlToRight).Column 'xlToLeft

  'set range from A1 to last column
   Set rng = wSheet.Range(wSheet.Cells(1, 1), wSheet.Cells(1, lastCol)) 

   'Outline the autofilter field hierarchy
   i = 1
   For Each cell In rng
     If cell.Value <> "" Then
       wSheet.Cells(cell.Row + 2, cell.Column).AutoFilter Field:=cell.Column, Criteria1:=cell.Value
     End If
   Next cell
End Sub

At the moment when the following code is executed:

wSheet.Cells(cell.Row + 2, cell.Column).AutoFilter Field:=cell.Column, Criteria1:=cell.Value

It returns Runtime error: 1004 Autofilter Method of Range class failed

Its probably something silly but im trying to figure out where im going wrong, I've tried looking on stackoverflow and even threw up a question but i havent had much luck

3
  • What is the purpose of your autofilter being in a loop? It's been awhile, but I'm almost certain you can only set the autofilter once. Meaning you have to turn it off and back on if you want to change criteria. Or if you are trying to create a multicriteria you have to build that first (with your loop) then do the autofilter after the loop.
    – gns100
    Commented Oct 13, 2022 at 15:05
  • its to run through the range and check if there is a value within that range and if so set an autofilter. (im sorry if the question description was vague) Commented Oct 14, 2022 at 7:26
  • 1
    Based on your response, I'm going to say that you need a break out of your loop after your first successful autofilter find. Again, I'm sure you CAN"T apply an autofilter on to something that is alreaday autofiltered. You'll have to reset on an unsuccessful filter. Hope this helps.
    – gns100
    Commented Oct 14, 2022 at 14:48

0

You must log in to answer this question.

Browse other questions tagged .