VBA excel - find column and filter it

Question

I'm new to VBA and trying to learn by myself for some work purposes. I've been trying to create a macro that will find a column in my worksheet and then filter it by a specific word. Usually I find codes in google and just edit them but I have troubles with this one..

What I was able to find:

Sub sorting()

Dim col As String, cfind As Range

Worksheets(1).Activate

col = "Type"

Set cfind = Cells.Find(what:=col, lookat:=xlWhole)

ActiveSheet.Cells.Sort key1:=cfind, Header:=xlYes

End Sub

Now I tried changing the "sort" part to autofilter. But it doesn't work at all..

.Range("A1:D1").AutoFilter Field:="col", Criteria1:="Virtual"

Could you please help? thanks! Coco

Answer 1

Function columnfilter(sheetName As String)

lastcolumnnumber = Worksheets(sheetName).Cells(1, Columns.Count).End(xlToLeft).Column

a = columnnumber_index(sheetName, "Your column name")
b = columnnumber_index(sheetName,"Your column name")
e = columnnumber_index(sheetName, "Your column name")
f = columnnumber_index(sheetName, "Your column name")

H = columnnumber_index(sheetName, "Your column name")
I = columnnumber_index(sheetName, "Your column name")
j = columnnumber_index(sheetName, "Your column name")
 

Debug.Print a
Debug.Print b
Debug.Print c
Debug.Print d
Debug.Print e
Debug.Print f
Debug.Print G
Debug.Print H
Debug.Print I


Worksheets(sheetName).AutoFilterMode = False

With Worksheets(sheetName).Range("A1")

.AutoFilter Field:=a, Criteria1:="value"
.AutoFilter Field:=b, Criteria1:="value"
.AutoFilter Field:=H, Criteria1:="<>value"
.AutoFilter Field:=f, Criteria1:="=value"
.AutoFilter Field:=I, Criteria1:="value"

  
End With



End Function

Function columnnumber_index(ByVal sheetName As String, columnname As String)

lastcolumnnumber = 0


lastcolumnnumber = Worksheets(sheetName).Cells(1, Columns.Count).End(xlToLeft).Column


For c = 1 To lastcolumnnumber

If Worksheets(sheetName).Cells(1, c).Value = columnname Then

columnnumber_index = c


Exit For

End If

Next c

End Function