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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi, Bruce,
how about this ?
Code:
Sub test()
    With Columns(1)
    .AutoFit
    .ColumnWidth = .ColumnWidth + 2
    End With
End Sub
kind regards,
Erik
 
Upvote 0
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!
 
Upvote 0
Sub Autofit2()
With Columns("A:F")
.AutoFit
.ColumnWidth = .ColumnWidth + 2
End With
End Sub

Hope this helps. Dan
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,696
Messages
6,103,357
Members
447,861
Latest member
LllopezXC

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