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

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,805
Messages
5,833,773
Members
430,232
Latest member
Testsubject

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