FILTER ON EXCEL TABLE IN VBA

Hi,

I need a vba code to create custom filters on excel columns within a table, any idea?
BOOK2.xlsm
LVL 1
joyacv2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven HarrisPresidentCommented:
Can you be a little more specific?  What are you wanting to filter by?  Is it always the same string, column, etc.?
0
joyacv2Author Commented:
hi,

For example in the columnA in the included workbook, filters the columnA for fields that contains letter V, but using VBA not table filter options
0
Steven HarrisPresidentCommented:
Are you familiar with VBA at all?  You can modify the below example as needed:

Sub FilterCriteria()
With Sheet1
    .AutoFilterMode = False
    .Range("A1:D1").AutoFilter
    .Range("A1:D1").AutoFilter Field:=1, Criteria1:="*V*"
    End With
End Sub

Open in new window


Line 2 - Needs to be the name of the sheet
Line 4 - Table Header Range
Line 5 - "*V*" searches for the letter V in a string


This works as expected in the example you provided.
BOOK2.xlsm
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

joyacv2Author Commented:
Hi,

I am familiar with VBA. How you specify by column name and not by range, this is because in my code the table can start in any cell and can be any number of fields in it?
0
Harry LeeCommented:
joyacv2,

Can you please be more specific on how the table will start in any cell? Do you mean by it can start in any column or do you mean it can start randomly in any place on the worksheet?
0
joyacv2Author Commented:
hi,

for example, the columnA of the table can be in the cell c3 or any other
0
Steven HarrisPresidentCommented:
How you specify by column name and not by range

We can refine this down:

Sub Macro3()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
        "=*v*", Operator:=xlAnd
End Sub

Open in new window


"Table 1" is the table (no matter where it resides).
"Field:=1" is the Header ID.  Ist column of the table is 1, second column of the table is 2, etc.
"Criteria1:=" is the same
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joyacv2Author Commented:
exellent, perfect!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.