Hi, this is Wayne again with a topic “Create an Excel Macro from Scratch: AutoFit Columns”.
In this article, i will show you how to create your own excel macro, and, specifically, in this article, it’s going to be a macro that will make it so that you can just click a button and by doing so all of the columns in your excel spreadsheet Will automatically fit the data? That’S in those columns and look at the spreadsheet that we’re using for this. It’S a contacts list with all sorts of personal information for some fictional people, and you can get a copy of this in the description below the video. But, as you can see, the widths of some of these columns are off a bit. This column b, for example, is way too wide and, of course, i can adjust that by clicking and dragging between column, b and column c. In this example, just right on that line, click and drag to resize that column there’s also a faster way to do this.
I could resize all of these columns by clicking and dragging to select all of the column headings, and then i could double click between any two of those and it resizes all of them to the perfect width. I just did ctrl z to undo so, even though that’s faster, i can create a macro to do it even faster than that. So how would i go about creating my own macro? Well, one way to do that is to record your own macro, and if you haven’t already watched my beginner’s guide to excel macros, you should watch that in a lot of cases, that’s the best option is just to record a macro and then assign it to a Button but let’s look at how to create a macro from scratch step. One is to make sure that you have the developer tab added to excel right. Now i don’t have that developer tab. There are a couple of ways that you can add that developer tab.
One of the easiest is just to right: click on any of these tabs and choose customize the ribbon. That brings up these options and you can look here at the right where it says main tabs. You can look for developer and make sure that box is checked. Then click! Ok! Now i have a developer tab here on the developer tab.
You can just look in the code group and there we have visual basic and go ahead and click that notice that there’s also a keyboard shortcut alt f11, and that opens the visual basic editor. Now, what is visual basic? Well, it’s a programming language like c plus, plus or other programming. Languages and microsoft includes visual basic, a version of it for you to use inside of microsoft, word powerpoint and excel, but instead of calling it visual basic, it’s vba, visual basic for applications, and so we’re going to do a little bit of programming here. But i hope you’ll find it to be pretty simple, not too intimidating. So what we’re going to want to do here is select sheet1. That’S the spreadsheet that i’m working on right now and if i double click that you’ll notice, that i get a window that pops up – and this is where i can put my code and if you’re familiar with visual basic – and you know how to code in it.
You can just type that code in and you’ve created an excel macro in this article, we’re just going to use a simple bit of code and i’m including this in the description below the video in case. You want to just copy paste it in so we’re going to start by typing. Sub sub stands for subroutine procedure and then i’m just going to type in auto fit all columns left parenthesis right. Parenthesis, i’ll, tap, enter, activate, enter cells, dot, select and notice.
When i typed dot, it brought up a pop-up menu that i could just click to pick which of these i want to use i’ll just go with select, so i can either keep typing or double click to enter. The word select there tap enter on the keyboard cells, dot, entire column, dot autofit, and here i can just tap, enter to choose autofit and then the last part is added automatically and sub, in other words, we’re done with this subroutine procedure. At this point, i’ll just go ahead and x out of the vba coding window and now on the developer tab in the code group. I can just go directly to the macros button and i can choose this one macro that i’ve created and click run notice. What it did it automatically resized all columns to fit exactly perfectly the content in those columns so that everything fits and the biggest item just barely fits in each column. Okay, i’m going to mess up some of these columns again, just to show you that there is an even better way to sometimes use a macro, and that is by creating a button and attaching that button to the macro that you’ve created.
So here on the developer. Tab, i’m just going to go to the insert button and i’ll choose a form control to add and it’s going to be a button. You can also add activex controls, but for simplicity, i’m just going to click form, control button and then click and drag on the screen to create the outline of that button.
Next, i’m supposed to assign a macro to that button. Now sometimes the macro doesn’t exist yet, in which case you can just click, ok and then assign the button later. But in this case i do have the macro.
It’S already created this autofit all columns. I click ok. Now i can change the name of the button. If i want to just click to highlight the text, delete it out and i’ll call, this button autofit columns next, i want to move this button off to the side so that it’s not covering the data.
Now, with these developer buttons, you can’t left-click and drag to move the buttons. Instead, you have to right-click and drag so i’ll. Just pull it over to the side, release the mouse button move here and then, once you click off that developer button, you’re not going to be able to move it or do anything other than click the button, unless you right click on the button that gives you The options you can format the control assign a different macro, cut it or copy it or whatever you need to do, but it’s the right click that enables those options. So i’m going to go ahead and left click on it and, let’s see if it executes the macro, i click and it worked. It beautifully resized all the columns to make sure everything fits perfectly once again now.
At this point, i want to save this workbook and this spreadsheet, because i’d like to be able to use this button in the future. I don’t want to have to create my macro and my button every time i use this spreadsheet, so i can just go up and click the save button, but watch what happens. It’S giving me a warning.
The following features cannot be saved in macro free workbooks, because i have created a macro and i’ve used visual basic for applications to create this great feature. I have to save this workbook in a special way notice. It says to save a file with these features. Click! No and then choose a macro, enabled file type in the file type list.
So i’ll do that. Click! No and then i’ll go up here to the file type and i’ll change it to excel macro, enabled workbook notice that it adds an m to the end of the file extension. I’M going to also change the name of the file itself, just a little bit just for my own reasons, and now, when i close this file, it closes out and then maybe in a day or a week, i go to file and i’ll choose that same spreadsheet. To open up there’s my autofit columns button that i created and linked to my macro but notice it’s not going to work unless i click this button to enable macros, so i’ll click enable content. And now, as i work, let’s say, i type in somebody’s address and it’s much longer than the other addresses, so the data doesn’t fit in the column anymore. I can just click this button and it will auto resize. So these visual basic scripts or bits of code that you can type in in a way they can seem almost like magic spells. Until you understand the coding itself, it does kind of seem like magic.
If i know these words and if i paste them into this window, it can do powerful things almost like magic, and that is one way to proceed from here. If you want to, you, can just search the internet for bits of vba code, plug them in and see how they affect your spreadsheets, now make sure you’re getting those codes from reputable sources and that you understand what the code is going to do. Otherwise, it could mess up your data in addition to just searching the internet. There are also some good books.
You can get and i’ll put links in the description below to some books about vba in excel, and if this video is well received, i’d love to make additional videos showing you some other useful, excel macros that you can build from scratch in microsoft. Excel thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow and subscribe, and when you subscribe click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, you can do that through my patreon account or by purchasing channel merch and you’ll, find links to that and more information below this video .