FILTER MULTI BILE columns

[BORUCH]

hi all

i would like an excle vba that would filter multiple columns based on a certain criteria.

i have 3 columns one is called "col1" the other one is called "col2" and the other one is called "col3" the columns will always be at row #14

but here is the problem for whatever reason, without going in to details here why that is, but my columns are always being moved around for ex. "col 1" header can be anywhere from lets say cell N14 to cell R14 so what i need is that the vba should look where that column header is and then filter it, the same with the other columns it can get moved around

my goal is for the vba to finds all headers "col 1","col 2", "col 3" and filter it based on certain criteria

any help is greatly appreciated

it would look something like this

Dim rngFind As Range
Dim rngCell As Range
Dim strColumn As String
Dim intFindColumn As Integer
Set rngFind = Cells.Find("col 1", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
If Not rngFind Is Nothing Then
If Target.Column = rngFind.Column Then....

but i don't know how to use this code for what i want

 

[DushiPunda]

I think the easiest thing to do is to use named ranges.

Repeat the following for each of your column headers:
1. Select the column header
2. Go to the Formulas tab (at the top of excel)
3. Click on Name Manager (in the Defined Names group)
4. Click New...
5. Type a name or use the auto-generated name. Note: "Col1", "Col2", & "Col3" are not valid names. So you'll probably have to name them "Col_1", "Col_2", & "Col_3" (It doesn't really matter what you name them. Excel will probably auto-generate the names to "Col1_", "Col2_", & "Col3_")
6. Change the scope from "Workbook" to the sheet that they reside on.
7. Click ok.

Now, no matter where they are on the sheet, when you refer to the column header by it's name, excel will know where it is.

So if Col1 is currently in N14 on Sheet1, then the following will return 14 (Column N):

Worksheets("Sheet1").Range("Col1_").Column

If Col1 is in R14, the above line will return 18 (Column R).

Hopefully that helps.

 

[BORUCH]

I think the easiest thing to do is to use named ranges.

Repeat the following for each of your column headers:
1. Select the column header
2. Go to the Formulas tab (at the top of excel)
3. Click on Name Manager (in the Defined Names group)
4. Click New...
5. Type a name or use the auto-generated name. Note: "Col1", "Col2", & "Col3" are not valid names. So you'll probably have to name them "Col_1", "Col_2", & "Col_3" (It doesn't really matter what you name them. Excel will probably auto-generate the names to "Col1_", "Col2_", & "Col3_")
6. Change the scope from "Workbook" to the sheet that they reside on.
7. Click ok.

Now, no matter where they are on the sheet, when you refer to the column header by it's name, excel will know where it is.

So if Col1 is currently in N14 on Sheet1, then the following will return 14 (Column N):

Worksheets("Sheet1").Range("Col1_").Column

If Col1 is in R14, the above line will return 18 (Column R).

Hopefully that helps.

i understand how to do the first part but how do i automate or even create the filter for these columns

 

[BORUCH]

i understand how to do the first part but how do i automate or even create the filter for these columns

any updates on my question

 

[Comfy]

I would suggest converting your table into an Excel Table (ListObject in VBA)

https://support.office.com/en-gb/ar...6c94334e492c?ui=en-US&rs=en-GB&ad=GB&fromAR=1

This makes refering to the table and it's headers very simple.

For eaxmple:

Sub FilterTbl()


Dim LO As ListObject
Dim Col1 As Integer
Dim Col2 As Integer
Dim Col3 As Integer


Set LO = ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1")


With LO
    Col1 = .ListColumns("col1").Index
    Col2 = .ListColumns("col2").Index
    Col3 = .ListColumns("col3").Index
    
   .Range.AutoFilter Field:=Col2, Criteria1:="0.170708625"
    .Range.AutoFilter Field:=Col1, Criteria1:="418"
    .Range.AutoFilter Field:=Col3, Criteria1:="841"
End With


End Sub

 

[BORUCH]

I would suggest converting your table into an Excel Table (ListObject in VBA)

https://support.office.com/en-gb/ar...6c94334e492c?ui=en-US&rs=en-GB&ad=GB&fromAR=1

This makes refering to the table and it's headers very simple.

For eaxmple:

Sub FilterTbl()


Dim LO As ListObject
Dim Col1 As Integer
Dim Col2 As Integer
Dim Col3 As Integer


Set LO = ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1")


With LO
    Col1 = .ListColumns("col1").Index
    Col2 = .ListColumns("col2").Index
    Col3 = .ListColumns("col3").Index
    
   .Range.AutoFilter Field:=Col2, Criteria1:="0.170708625"
    .Range.AutoFilter Field:=Col1, Criteria1:="418"
    .Range.AutoFilter Field:=Col3, Criteria1:="841"
End With


End Sub

In my situation here i cant convert it to a table

what i'm trying to do is filter multiple columns simultaneously with one criteria lets say the word "box" it can exits in all three columns

i thing going with vba to find column header is a better option
any help is appreciated

 

[BORUCH]

in my situation here i cant convert it to a table

what i'm trying to do is filter multiple columns simultaneously with one criteria lets say the word "box" it can exits in all three columns

i thing going with vba to find column header is a better option
any help is appreciated

any updates?

 

[BORUCH]

Sub Sample()
Dim ws As Worksheet
Dim rng As Range, rngA As Range, rngB As Range, rngC As Range
Dim Lrow As Long


Set ws = Sheets("Sheet4")


With ws

Lrow = .Range("A" & .Rows.Count).End(xlUp).Row


Set rng = .Range("A1:C" & Lrow)


.AutoFilterMode = False



With rng
.AutoFilter Field:=1, Criteria1:="*BOX"
Set rngA = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
End With
.AutoFilterMode = False
With rng
.AutoFilter Field:=2, Criteria1:="*BOX"
Set rngB = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
End With
.AutoFilterMode = False
With rng
.AutoFilter Field:=3, Criteria1:="*BOX"
Set rngC = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
End With
.AutoFilterMode = False
rng.Offset(1, 0).EntireRow.Hidden = True
Union(rngA, rngB).EntireRow.Hidden = False
End With
End Sub


how can this code be modified for it to find the column header so we can solve my problem ?