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!
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!
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!
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!
