VBA to autofit columns with added space

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
365
I am using a VBA to auto fit the widest entry for a range of columns. I am wondering if there is a way to add some space to the auto fit so the columns are not so tight to the text. If some one could lay out the format I would appreciate it.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi Shadkng.
You could try something along this line.

Code:
Sub ColumnWidth()


Columns("A:A").AutoFit
Columns("A:A").ColumnWidth = Columns("A:A").ColumnWidth + 2


End Sub

This will work with one column at a time, and will not work with multiple selections.
 
Last edited:
Upvote 0
I found this on a youtube video and it seems to work. But I don't really understand the code. Do you see any problem with it? Thanks

Sub AUTOFIT_COLUMN()
Application.ScreenUpdating = False
Cells.EntireColumn.autofit
For i = 1 To ActiveSheet.UsedRange.Columns.Count
Columns(i).ColumnWidth = Columns(i).ColumnWidth + 3
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
What does this mean:

This will work with one column at a time, and will not work with multiple selections.

Do you mean if you select column A To G

Or do you mean Column B Column G Column J all at once


 
Upvote 0
Well this means it will do this on all columns with data.
We can only help if you give more specific details.
You never mentioned what column or only selected columns.



I found this on a youtube video and it seems to work. But I don't really understand the code. Do you see any problem with it? Thanks

Sub AUTOFIT_COLUMN()
Application.ScreenUpdating = False
Cells.EntireColumn.autofit
For i = 1 To ActiveSheet.UsedRange.Columns.Count
Columns(i).ColumnWidth = Columns(i).ColumnWidth + 3
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
@Shadkng
Don't see any issues with that code....try it and see what it does.
If you don't like the result, close the workbook without saving.
 
Upvote 0
Why not just format the column(s) with a leading and/or a trailing space?
 
Upvote 0
That code seems to work perfectly as it only formats the columns that have data it them, I think. Thanks everyone for the input.
 
Upvote 0
Hi, below is partial code related to the previous threads. I want range (H:X) to auto fit but also add padding. The loop part of the code adds the padding for all columns. Can we change the loop to add padding for only columns (H:X)? Thanks

Code:
Sub DETAIL_FORM2_COPYROW()
    Application.ScreenUpdating = False
    Sheets("DETAIL FORM2").Activate
    Range("A30:X1500").ClearContents
    Rows("29:29").Copy
    Rows("29:" & Range("B1").Value).Select
    Selection.PasteSpecial xlFormulas
    Selection.PasteSpecial xlFormats
    Range("H:X").Columns.AutoFit
    
    'For i = 1 To ActiveSheet.UsedRange.Columns.Count
        'Columns(i).ColumnWidth = Columns(i).ColumnWidth + 3
        'Next i
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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