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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920