Create filter based on cell value-CodePudding

Hi im trying to create a function in VBA which scans the top row and inserts a filter on a particular cell in the third row if the corresponding cell in the top row contains a value, if a cell is empty then it should skip to the next cell. The third row will be a header row.

Here is some code:

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

Set wSheet = Worksheets("Machining")
'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(Cells(1, 1), Cells(1, lastCol)) 'will be a higher cell range

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

CodePudding user response：

Try this:

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