VBA to autofit columns with added space

[Shadkng]

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.

 

[footoo]

Front space and back space : _)@_)

 

[ExcelEdge]

Hi Shadkng.
You could try something along this line.

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.

 

[Shadkng]

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

 

[My Aswer Is This]

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


​

 

[My Aswer Is This]

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

 

[Michael M]

@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.

 

[footoo]

Why not just format the column(s) with a leading and/or a trailing space?

 

[Shadkng]

That code seems to work perfectly as it only formats the columns that have data it them, I think. Thanks everyone for the input.

 

[Shadkng]

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

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