CODEWITHSUNDEEP
excelvba
Dando276
Dando276

Reputation: 13

Create filter based on cell value

I am trying to create a function 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 of my 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

It creates filters for the first three header cells regardless of what is in the cell above.

Upvotes: 1

Views: 141

Answers (1)

CLR
CLR

Reputation: 12279

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

Upvotes: 1

Related Questions