Hi,
i have source table like this (TOPO1 and TOPO3 are inoutted variables which can be changed)
Screenshot_8.png
and i want to loop through table and output result like here (based on Input Values)
Screenshot_9.png
What is the fastest way to do this?
Best,
Jacek
You have already set to Filter the table, click on the drop down and unclick those items you don't wish to see.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Why do you want to use VBA
Just enter Topology in G1
Select your table and use a Data Advanced Filter, Filter in Place and specify G1:G3 as the criteria.
If you want the details output to another area then enter the two column labels somewhere and instead of Filter in Place choose the Copy to another location and select the two column labels.
See attached
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi,
than you very much.
i have output result into separate worksheet.
So advanced filter it is the best option in VBA also ?Can you help with code?
This is only part of macro so i have to use VBA here.
Please help,
Jacek
Use the macro recorder to do what you want, then post your code here and explain what needs to modified in the code.
Hi alansidman,
I do not want to use macro recorder - it is not the best solution and not good approach.
Goal is clear. The output range should be copied from table.
And question also is clear - what is the best option to do this?
Use advance filter in VBA?
Best,
Jacek
Will the input values always be in G2 and G3? Will it be more than two? How to handle if less than two or more than two. Need to know specifics. That is why I asked that you use the Macro recorder and then we would adapt it. Since you don't wish to help in that manner, then need to know specifics. Be as detailed as possible as you are aware, VBA is not generic.
ok, i understand, thank you.
Yes, input values will be in column G, you can have the list of filtered values there, it can be 1 and more.Will the input values always be in G2 and G3? Will it be more than two?
So generally - you have the list which will be the base for getting listrows from table and copied into second worksheet.
Best Wishes,
Jacek
Here is a VBA solution for you.
Change up the paste range as needed.
Please Login or Register to view this content.
Thank you very much,
seems to be interesting approach.
What do you say to use dictionary and listrows?
Best,
Jacek
Not familiar. But if it works for you, then in the words of NIKE, "Just Do It."Please Login or Register to view this content.
Thank you very much.
Ok your method if i will have thousends of rows can be slow because you are copying bunch of specific rows.
Do you think advance filter will be faster?
Jacek
Test it and make a determination. I think filtering is always faster, but try out both and see.
Link to multi filter VBA-->https://trumpexcel.com/vba-autofilter/
Last edited by alansidman; 05-18-2019 at 03:12 PM.
thank you very much.
Best,
Jacek
OK, VBA AdvancedFilter.
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.
thank you bakerman2,
can i provide criteria not in range but in memory?Please Login or Register to view this content.
I mean create range criteria on the fly directly in a code and not refer to worksheets cells?
Jacek
I haven't seen it done yet.
thank you.
I will keep this post opened because i do not think so that provided code is the fastest way to copy filtered values from table.
Maybe advance filter but advance filter has also restrictions...
Jacek
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks