Updated June 12, 2023
PART- 13 – Visual Basic Commands – Data Filter and Dynamic Update
Video Transcript – Visual Basic Commands – Data Filter and Dynamic Update
Video on Visual Basic Commands – Data Filter and Dynamic Update – For looking at the code, we will click here on the visual basic editor, or maybe you can directly look at from a word Macros, and you can click on sector selection which is only one here, and we go to edit this is present under sheet 1 Modules this is what the code the all about so codes name is sector selection, and this is very small code but very interesting one as well so let look at what it is actually doing the 1st one is range A1. Select so we were on the sheet called book 1, and we were inside this sheet 1, so first thing that edit was to select A1 as the cell then selection. Auto filter, so that were we had gone to data and data auto filter, and that what this command is all about then it says active sheets. Range so this is the full range from A1 till A13 auto filter, so this is the range which we are talking about. This is fell no 2, and criteria is back, so this is feel no1, and this is feel no 2 and here the criteria is banks so it’s looks likes like you know the code is beat logically in nature, so even if we have to kind of manipulated we should be able to do it with not much difficulty then what happens is once we kind of filter it say for example for banks this is were we get so it says range A1. B7. Select so it ask for selection of this and then selection. copy, and then once we have copied this range which is CTRL C it ask for opening sheet 2 because that what is command is all about, so it ask for move into sheet 2 and then again select range A1 and active sheet. Paste so it paste whatever it has selected so code actually it kind of very simple so now that we have looked at the simple virgin of this macro let us introduce some of the complexity and understand even more newances of the Macro applications so let go our excel sheet and what we had here was the name of the company and the sectors right there were 3 banks 3 material companies 3 software and 3 utilities companies now let assumed that you know this list is kind of dynamic in nature so what I mean by dynamic is today we have a 3 companies each may be you know we add 2 banks late may be tomorrow after we have written the code we update 2 more banks here so say example there is 2 addition here banks 1 and bank 2 and the classification is obviously banks these are 2 addition now I am assuming at this stage that the Macro which we had created and if he sort using banks obviously I am assuming that these 2 should also get exported as an output so let me try and do that it is happening in this case of Macro or not so for running a Macro I’ll go to Developers ALT F8 or going to the Macro selection and then I’ll run the Macro as you can see and I was expecting the same that only 3 companies have actually shortlisted from the list of 5 banks there were 5 banks right they were 3 here and 2 here however the once which were out put were only 3 so what has wrong with our code so probably we may have to kind of investigate our code and kind of make it more dynamic in nature so let go back to visual basic editor and see what was going on in the code itself so as you can see here this line were after we have done the filtration process it says range A1 to B7. Select ok and I’ll show you what happens here once we select banks we see that this is the range A1 till B7 but this actually till B15 right so we have already missed the 2 banks because of this restricted nature of the formula so ideally this should have been B15 so we want to some way in which you know we can update this dynamically so let’s take help from excel again and you know again as we go back to same logic we will try and create a code using excel and then logically manipulate if required so I will go back to our default state and record a new Macro so this time I will probably call this as filter dynamic kind of Macro so let do that I’ll go to Developer Tab I am going to record Macro and let me call this as filter dynamic ok and I am clicking ok as off now so the first step that we did last time that go to A1 so that what would be similar and then I’ll click on filter 2nd step obviously is saying that I may want to select banks and I’ll click ok this is step no 2 how I will are step no 3 actually change see I’ll tell you how it may change the 3rd one was to select the range and copy so last time what we have done was we had you know manually selected this so if manually we these so if manually select these are code will still work but tomorrow again those banks 3 bank 4 bank 5 probably we will land up same problem so this is temporally solution so we should have dynamic solution to our problem so solution lies here in using this formula the formula is CTRL SHIFT and star this is what you must use CTRL SHIFT and basically CTLR SHIFT and 8 movement you do this you will find that the whole range gets selected so CTRL SHIFT and 8 that what I am doing from my keyboard CTRL SHIFT I am pressing 8 and this whole range gets selected this time ok so that is the only difference I am introducing in this dynamic filter then I would do a CTRL C again I’ll go to sheet 2 click on A1 and then CTRL V so and probably stop the Macro right so this is what I have done and let me delete this Macro and just check for the movement that if this Macro working fine at least in case or not so I’ll go to Macros and then there is Macro which is filter dynamic I’ll run this macro and you can see that bank 1 and 2 is absolutely fine now let do some here let me delete this and include another bank, bank 3 and bank 4 so there are 2 new banks which I have now introduce after I have created macro now I am assuming that probably things would be fine so let’s go to Macro now and click on filter dynamic and let me run this macro and yes so you can see that bank 3 and bank 4 are now part of the whole list so as we can see we have done something new in dynamic way so obviously good way to look at is look at code itself so I’ll go to macros and understand what is different in this case so I am clicking here on edit this is the code of filter dynamic and this is was the code of sector selection which was the old one now let look at difference in this case it says all these 3 field actually are essentially same the thing which has kind of change is these things called selection. current region. Select ok, so selection. Current region. select is a new term that gets introduce when we use things thinks call CTRL Shift and 8 ok, now another thing to note is that since because I was wondering here and there probably, I had selected cell A1 again. We were already on cell A1 I had actually place my cursor here to start with the we had done selection auto filter active sheets and introduce then I’ll again saying that range A1. Select so there 2 times which I have set range A1. Select for the same worksheet, so I don’t think this is actually require here this is just unnecessary code that was introduce because I was doing something else in between, and from here to here, I had actually clicked on between, so that what this code has introduce some additional lines because of my issues there so this is how the overall dynamic code would actually work.
