COLUMN WIDTH

BruceEdwards

Board Regular
Joined
May 28, 2004
Messages
231
I have been using AUTOFIT to set the column width, using VBA. However, I don't quite like the look that Autofit gives, I would like the columns to be slightly wider, maybe 2 or 3 spaces.

Is there any way to do this in VBA? I cannot figure out how to do it.

Essentially, what I am thinking is that the macro would specify Autofit, then somehow, go back and increase column width by a factor of say, 3 spaces. So the final width would be "Autofit + 2 "

Another possibility I thought of was if a macro could be written to count the number of characters in the widest cell in a column, then increase by a factor.

Any ideas??
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Bruce,
how about this ?
Code:
Sub test()
    With Columns(1)
    .AutoFit
    .ColumnWidth = .ColumnWidth + 2
    End With
End Sub
kind regards,
Erik
 

BruceEdwards

Board Regular
Joined
May 28, 2004
Messages
231
Erik:

Thanks very much. This works . But, the way you have written it:

WithColumns(1)

It will perform the operation only on Column A. I have played around with it and I can't get it to do what I want, which is to get ot to work on several columns.

Let's say I want to perform this on Columns A through F, how would I do that?

Thanks for your help, you have helped me a few times in the past and I really appreciate it!
 

dantb

Active Member
Joined
Mar 20, 2002
Messages
358
Sub Autofit2()
With Columns("A:F")
.AutoFit
.ColumnWidth = .ColumnWidth + 2
End With
End Sub

Hope this helps. Dan
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Hi, Dan!
did you test the code your provided ?
for me it will only work if all columnwidths are the same after "autofit"
try this
Code:
For i = 1 To 6
With Columns(i)
.AutoFit
.ColumnWidth = .ColumnWidth + 2
End With
Next i

kind regards,
Erik
 

dantb

Active Member
Joined
Mar 20, 2002
Messages
358
Hello Erik: I took a blank workbook and inserted a modual and the below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Autofit2
End Sub

Using Excel 2002 SP2 version everything seems to work fine, I did not have auto fit selected. Seeing how the code is supposed to be written I like your version better. Still have a lot to lean but try and figure as much out as I can by trial and error. Thanks Dan
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

fill in some data
like
A1: "a"
B1: "aa"
C1: "aaa"
D1: "aaaa"
E1: "aaaaa"
F1: "aaaaaa"
then run the code
you will notice the columns won't get larger using your code
filling in "aaaa" for all 6 cells they will, because the widths are the same

do you get the same results ?
 

dantb

Active Member
Joined
Mar 20, 2002
Messages
358
I see what your saying. Its autofiting the text in that range but it is not adding the 2 extra blanks at the end. Thanks Dan
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,686
Members
412,481
Latest member
nhantam
Top