VBA to autofit columns with added space

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
269
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
Front space and back space : _)@_)
 

ExcelEdge

New Member
Joined
Apr 19, 2016
Messages
17
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:

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
269
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,528
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

@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

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
Why not just format the column(s) with a leading and/or a trailing space?
 

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
269
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

Active Member
Joined
Oct 11, 2018
Messages
269
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
 

Forum statistics

Threads
1,136,315
Messages
5,675,038
Members
419,546
Latest member
RobWayCot

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
Top