Learn Excel from MrExcel - "AutoFit Plus A Little Bit": Podcast #1716

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on May 23, 2013.
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. VBA and Macros Excel 2013

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. VBA and Macros: Microsoft Excel 2010

"The Learn Excel from MrExcel Podcast Series"

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


Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1716 - AutoFit Plus a Little Bit!
Alright, today's question sent in from YouTube, from Mark.
Mark is trying to do an AutoFit macro, every time the worksheet changes would do Cells.EntireColumn.AutoFit, but he wants to add a little padding to make the cells a little bit more readable.
Alright so Mark sent in this macro, let's copy this Ctrl+C, and we'll go back to Excel.
We have our sheet here, this is Sheet1, so when i switch over to VBA, Alt+F11, I want to double click on Sheet1 and paste that code there.
It's not going in a regular code module, it is going in the Worksheet Change event here, right on the Worksheet module.
Now let's see how Mark's macro works.
So I just changed something on the sheet, and it makes everything wide enough for the widest value in the column.
But Mark is looking for a little bit of extra padding, so here's how we modify Mark's code.
We're going to say For i, variable, = 1 to ActiveSheet.UsedRange.Columns.Count . Alright, that way we're not going through and changing all 16000 columns, we're just changing the columns that are actually in the used range.
And we'll say Columns.I.ColumnWidth = Columns(i).ColumnWidth + , let's say 1, I don't know, maybe the 0.5 would work better, you could experiment with that a little bit.
And so, now it does the AutoFit, and it will go through and change each column, one at a time, to be a little bit wider.
Try it again, I'll put in I2, and now, are we actually seeing the columns shrink and get bigger?
I figured we would, but it's actually going pretty fast.
But just in case let's do Application.ScreenUpdating = False.
And then at the end Application.ScreenUpdating = True.
That should prevent us from seeing the flash, I'll type something in here, let's put in something really long, and it just automatically- I like that, that that's a good little macro, wow, it's quick, you'd have to add this code to each sheet that you wanted to work on.
Great idea from Mark, and a little bit of extra code there to add a bit of padding.
Well hey, I want to thank Mark for sending that question in, and I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
 

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top