Deducting the column width in number of characters from a spreadsheet

JohanXL

Board Regular
Joined
Jun 13, 2015
Messages
78
"On a worksheet, you can specify a column width of 0 (zero) to 255. This value represents the number of characters that can be displayed in a cell that is formatted with the standard font. The default column width is 8.43 characters."

I have the following question:

If I have a spreadsheet with different column widths, is it possible in some way to display what those exact column widths are, i.e., to know exactly how many characters wide the different columns on my current spreadsheet are?

Thank you in advance for any tips.

JohanXL
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
This macro will put the column widths in a specific row. Change the output row number 10 to suit.

Code:
[color=darkblue]Sub[/color] ColWidth()
    [color=darkblue]Dim[/color] col [color=darkblue]As[/color] Range
    [color=darkblue]For[/color] [color=darkblue]Each[/color] col [color=darkblue]In[/color] ActiveSheet.UsedRange.Columns
        Cells([COLOR=#ff0000]10[/COLOR], col.Column) = col.ColumnWidth         [color=green]'Change the 10 to the desired output row number[/color]
    [color=darkblue]Next[/color] col
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

JohanXL

Board Regular
Joined
Jun 13, 2015
Messages
78
This macro will put the column widths in a specific row. Change the output row number 10 to suit.

Code:
[COLOR=darkblue]Sub[/COLOR] ColWidth()
    [COLOR=darkblue]Dim[/COLOR] col [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] col [COLOR=darkblue]In[/COLOR] ActiveSheet.UsedRange.Columns
        Cells([COLOR=#ff0000]10[/COLOR], col.Column) = col.ColumnWidth         [COLOR=green]'Change the 10 to the desired output row number[/COLOR]
    [COLOR=darkblue]Next[/COLOR] col
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Thank you very much, AlphaFrog!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,960
Messages
5,508,411
Members
408,682
Latest member
alifarhat

This Week's Hot Topics

Top