PDA

View Full Version : Modify VBA code for Advanced Filtering



Younes
01-25-2017, 04:23 AM
Hi everyone, I'm new member here, hope to help me in this.
Kindly see attachment file which has VBA Code

What this code is doing?:
Filtering rows (records) based on two things,
1- the first letter or letters entered in cell H1
2- column's(field's) number entered in cell G2
---------------------------------------------------------------------------------------------------------------------------------


What I want ?:
Ignore enter the Column(field) number in the cell G2 and modify the code to be clever code which can capable to selecting the appropriate Column(field) for filtering_
by reading the letters entered in cell H1 and compare it with the contents of all columns (fields),_
and then selects all the rows(records) which it applies input in H1 regardless of the column.
Thanks for All

offthelip
01-25-2017, 04:48 AM
Unless I have misunderstood what you are trying to do, I don't think you can do what you want just using the EXcel filter unless you create an extra column to filter on, This is because filtering in Excel is always an "AND" function with multiple columns, there isn't anyway to do an "OR" function without creating an extra column. Your requirement needs the "OR" function"
So what I would do is run a macro to search each column for the string, if it is present put "TRUE" in the extra column, then after running the macor you just filter on that column.

snb
01-25-2017, 05:23 AM
What is your goal ? Why filtering on letters ? (for gender e.g.)

Younes
01-25-2017, 10:17 AM
I think you have good logical thinking, you are right. you give me nice ideai, ill try it. thanks a gain.


Unless I have misunderstood what you are trying to do, I don't think you can do what you want just using the EXcel filter unless you create an extra column to filter on, This is because filtering in Excel is always an "AND" function with multiple columns, there isn't anyway to do an "OR" function without creating an extra column. Your requirement needs the "OR" function"
So what I would do is run a macro to search each column for the string, if it is present put "TRUE" in the extra column, then after running the macor you just filter on that column.

Younes
01-25-2017, 10:21 AM
thanks for replying, the file which I attached was just sample. of course there is logical reason to filtering on letters. thanks a gain.

What is your goal ? Why filtering on letters ? (for gender e.g.)

p45cal
01-25-2017, 12:12 PM
Try the attached.
Delete H1 contents to restore the full unfiltered list.

What you had in your file wasn't Advanced Filter, it was automation of Autofilter.
Whereas what is in the attached IS Advanced Filter, automated.
The criteria range is P1 to P6 but it can be squirrelled away anywhere after tweaking the code.
P4 contains a slightly different formula because column C (Age) is numeric. You might want to tweak that formula.

Younes
01-25-2017, 12:17 PM
today I'll upload new file with my solution.


thanks for replying, the file which I attached was just sample. of course there is logical reason to filtering on letters. thanks a gain.

Younes
01-25-2017, 12:18 PM
today I'll upload new file with my solution.

Younes
01-25-2017, 12:26 PM
Hi
I find a solution for filter a table without select any column, just enter any letter/s or number you see in any cell in table to filtering. hope its useful.
Thanks

snb
01-26-2017, 02:43 AM
Please do not Quote !

Younes
01-26-2017, 04:29 AM
Thanks a lot. good works