Need help using AutoFilter on a Range in VBA

[califrania]

Hi!

Row 3 has a column header labeled "Description" and I want to filter out all cells containing the words "tech" & "qa" in that column. My macro can find the column and the DesRng can find the correct cells

But..my last line of code (with the Auto Filters) will not work
I think this is because all the cells contain formulas that pull the text in, rather than the text actually living in cell - but idk how to work around this.

Any help appreciated!


Dim DesCol, LastRow As Integer
Dim DesRng, As Range


Cells(3, 1).Select


Do While ActiveCell <> "Description"
ActiveCell.Offset(0, 1).Select
Loop
DesCol = ActiveCell.Column


LastRow = Cells(rows.Count, DesCol).End(xlUp).Row
Set DesRng = Range(Cells(3, DesCol).Address(), Cells(LastRow, DesCol).Address())


DesRng.AutoFilter Field:=1, Criteria1:="<>*tech*", Operator:=xlAnd, Criteria2:="<>*qa*"

 

[Takae]

Hi,

Are you going to filter only one column?
It seems your spreadsheet has data from columnA.
>Set DesRng = Range(Cells(3, DesCol).Address(), Cells(LastRow, DesCol).Address())
I think this line is not necessary.

ActiveCell.AutoFilter Field:=DesCol, Criteria1:="<>*tech*", Operator:=xlAnd, Criteria2:="<>*qa*"
Please change this line, and try.

 

[ukmikeb]

Hi

Try :-

Dim DesCol As Long, LastRow As Long ' Always define rows and columns as Long
Dim DesRng As Range

'Cells(3, 1).Select ' you do not need to select cells

'Do While ActiveCell <> "Description"
'ActiveCell.Offset(0, 1).Select
'Loop
DesCol = Application.WorksheetFunction.Match("Description", [A3:CZ3], 0) ' Alternative method of finding a column header


LastRow = Cells(Rows.Count, DesCol).End(xlUp).Row
Set DesRng = Range(Cells(3, 1), Cells(LastRow, DesCol))

  DesRng.AutoFilter ' reset any filters already on the sheet
  DesRng.AutoFilter Field:=DesCol, Criteria1:="<>*tech*", Operator:=xlAnd, Criteria2:="<>*qa*"

hth