00:00I recently published a video on how you can auto adjust the column width as you
00:04enter more character on your particular column itself but someone had a question
00:10if I can apply this same kind of feature for the entire workbook not just one
00:15worksheet itself well this is how you do it first you're gonna right click of any
00:20of this cell here and from the context menu you're gonna select view code in
00:24here you're gonna double click on this workbook like this make sure you're
00:28working on this workbook and then you press ctrl V to paste this VBA script if
00:33you would like to copy of this VBA script do leave a request on the comment section
00:37of this video let me give you a overview of what this particular VBA script does
00:41the first statement here basically disable any events let's say for example if
00:46you're trapping and selection change or cell change on your VBA this basically
00:51disables it and allows this VBA script to run accordingly and this section here
00:56prevents any calculation to automatically calculate so everything is done
01:00manually but this two flags are turned on as soon as all this main section of the
01:06subroutine is completed now looking at the main subroutine WS is basically a
01:11worksheet variable definition and this for loop what it does is that it goes
01:16through every single sheet on this workbook and then basically if that
01:21particular workbook on the for loop matches what you have been modifying it
01:26then that cell will do an autofill rather the column width adjustment using this
01:31statement here but if you want all the worksheet on this workbook to be affected
01:37basically what you do is you get rid of this two statement here and you'll
01:41affect all the worksheet here let me show you what I mean by that so we'll start
01:45off with working on only the active worksheet so that it affects that
01:50particular worksheet here so I'm gonna press ctrl s to save and then close your
01:54VBA editor here now if you were to enter let's say a different column like that
02:00you can see that the column width auto adjust by itself let me show you one more
02:04time here again you can see that the column width automatically adjust also
02:09works on if you enter the entry on your actual data set yourself now you can see
02:15that if you go back to this guy here and you adjust this and squeeze this thing
02:19here and you go back to this one here I'm gonna go ahead and delete this two
02:23column because this two was not required and basically changes to something very
02:30big here maybe you can see that I've made changes to this particular column width but
02:37if you look at the previous worksheet this thing is not affected at all let's go
02:42back to our VBA editor here and then go view code and go this one here now like I
02:49said if you were to remove this two code like that now this will affect the entire
02:56workbook regardless of whatever sheet or whatever cell values you make changes to
03:01any sheet here like for example you can see this is still crushed in together but if I
03:05want to change this to say something very large of course this particular sheet it adjusts the
03:12column width but if you go back to the previous one you can see that the column width has also
03:17been
03:17adjusted
03:17you
03:18you
03:20you
03:20you
03:20you
Comments