Excel - AutoFit Plus A Little Bit: Episode 1716

4,243 views · Published 23 May 2013 · 2:53 · Indexed 14 November 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to Use AutoFit with Extra Padding in Excel - MrExcel Podcast.

Welcome to another episode of the MrExcel podcast! In today's episode, we will be discussing how to use the AutoFit feature in Excel with a little bit of extra padding. This question was sent in by Mark through YouTube, and it's a great one. Mark wants to create a macro that will automatically adjust the column width every time the worksheet changes, but with a little bit of extra space for readability. Let's dive in and see how we can make this happen.

First, let's take a look at the macro that Mark sent in. We'll copy it and paste it into the VBA editor by pressing Alt+F11. Now, instead of pasting it into a regular code module, we'll double click on the Sheet1 module and paste it there. This will ensure that the macro runs every time the worksheet changes. Let's test it out by making a change on the sheet. As you can see, the column width adjusts to fit the widest value in the column. However, Mark wants a little bit of extra padding, so let's modify the code to make that happen.

To add a bit of padding, we will use a For loop to go through each column in the used range and adjust the column width. This way, we won't be adjusting all 16,000 columns, just the ones that are actually being used. We'll also add a small value, such as 0.5 or 1, to the column width to create the desired padding. And just to make sure we don't see any flashing on the screen, we'll add a line of code to turn off screen updating while the macro runs. And there you have it, a simple yet effective macro that automatically adjusts column width with a little bit of extra padding.

I want to thank Mark for sending in this question and for giving us the opportunity to learn and share this useful tip. And thank you for tuning in to this episode of the MrExcel podcast. Don't forget to subscribe to our channel for more Excel tips and tricks, and we'll see you next time for another netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Mark's AutoFit Macro
(00:26) Implementing Mark's Macro
(00:36) Modifying Mark's Code
(01:08) Limiting to Used Range Columns
(01:27) Adding Padding to Columns
(01:53) Testing and Improving the Macro
(02:18) Preventing Screen Flash
(02:38) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
ActiveSheet.UsedRange.Columns.Count
Adding padding to column width
Application.ScreenUpdating
AutoFit macro
Cells.EntireColumn.AutoFit
Column width
Excel worksheet change event
Experiment with column width
Flashing screen prevention
VBA code module
Worksheet module


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153538/


Marc has an 'AutoFit Macro' and wants to add a little bit of padding in the Cell as the Macro performs. Taking the original Macro, Bill shows us how to address the Worksheet and the specific Range to be adjusted so that we can add a little code that will produce some padding when the Macro runs. Follow along with MrExcel in Episode #1716 to see how this is done.

Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. http://www.mrexcel.com/store/index.php?l=product_detail&p=257 
 
For more information on Excel 2010 VBA and Macros, check out...

VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens - or even hundreds - of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder. http://www.mrexcel.com/store/index.php?l=product_detail&p=186 

"The Learn Excel from MrExcel Podcast Series"

Visit us: MrExcel.com for all of your Microsoft Excel Needs!

More from this channel