I have to filter and rearrange a table.
I have already a filtered table working well to filter the main table on some criterias.
However i need to rearrange the order of the column and extract only some of them.
Here is attached the table including the macro on the filtered one which shows also the extracted information needed and the correct order of the column as required,
could you assist ?
Last edited by mariec_06; 06-23-2025 at 11:21 AM.
Hi,
your headers name in main table are not similar to the filtered table, for example, i can´t find "Type d'avenant" in the main table? Please correct this!
Your filter criterias are in filtered table A2:E2, right?
So please, put 2-3 rows with data in the table, of course anonymized and upload you sample file again, then we can do the vba code!
br, Alexandra
Best regards, Alexandra
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved"
the header in the filter table are the same than in main table, just they are not in the same order and only some of them appear, this is how the filtered table is expected to look like
the filtering macro is working properly but it repeats the main table as such with all columns etc but i would like to rearrange it as per attached file
Here attached the modified file showing the expected results if for ex we apply as criteria, the boats of less or equal to 15M, and the result of the cell from main table
I have removed Column O - Apporteur Location from filterheaders because it's not present in your main table.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
works perfectly thanks....
One small thing, how can i arrange the code so that if one of the filter is clear then it considers only the remaining one
For ex if i put a filter boat value < 5000000 and type d'avenant "surprime" but i clear one of the two then it updates the filtered range appropriately
but if all filters field are empty, then it clean the content
Like this then.
Please Login or Register to view this content.
Thank you very much for all this.
would it be possible to apply same code if the filtered datas are in this workbook but the datas of sheets "main table" are actually in another workbook (with has only one worksheet), basically i want to use this filter sheet as a separate workbook to open briefly the datas sheet, perform the autofilter populate the results here and close the datas workbook.
i was thinking of below code but got an error obivously got an error "AdvancedFilter method of Range class failed"
Please Login or Register to view this content.
Last edited by mariec_06; 06-25-2025 at 06:12 AM.
You did not refer properly to the opened workbook when searching lRow & setting rngToFilter.
Please Login or Register to view this content.
I have still error message
As I already mentioned before, check all filterheaders in Row 4. They should be exact duplicates from xlsx file.
they are, and it is working perfectly when both datas + filter are in the same workbook with below code
But instead of autofilter the Sheets("Avr 24-25") i would need to apply the autofilter on the workbook "01 04 2024 au 01 04 2025"
Please Login or Register to view this content.
No, they are not. Even the slightest typo or space after a title causes the code to error out.
I got your previous file to work because I copied them one by one from Sheets("Avr 24-25") to the Filter sheet.
I'm not going to test 32 columns for you.
of course not, i found the issue and all is working really well sorry for that
i have one small issue, i don't know how to put as "Boat value" criteria the and formula,
if i put a criteria such as <5000000 it is working but i need to filter it now with a range number and when i put >5000000<10000000, i tried also ="=if(and(>5000000,<10000000), true, false)" nothing is working....basically i don't know how to translate in the criteria formula "if boat value is between two values"....
To do that you need Boat value twice in your selection criteria. In the first you put >value and in the second <value.
mariec.JPG
Don't forget to adjust the search criteria range in the code because it expands with 1column
Last edited by bakerman2; 06-27-2025 at 04:22 AM.
thank you, will same apply to any field on which i want to input two criterias?
Take a look at following link.
https://excelmacromastery.com/vba-advanced-filter/
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks