Create filter based on cell value

user_19459070 0
Viewed 24

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
vba excel
1 Answers

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
user_74467600
Related