How to make Excel report width of columns(s)?

[Legacy 32120]

Hi,

I'm looking for a macro or function/formula to list & total a range of column widths. Having lots of columns with varying widths t'would rather not have to do it manually from each column's Properties.

Thanks....

Max

 

[tnazirov]

Hi,

Try formula:

=CELL("width",A1)
=CELL("width",B1)
etc.

 

[Bill Rockenbach]

=CELL("width",A1)
Returns the column width rounded to a whole number

Set a user defined function up in a standard module then use it to find the column width.

Function CellWidth(Col As Range)
CellWidth = Col.ColumnWidth
End Function

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 98px"><COL style="WIDTH: 280px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD></TD><TD style="TEXT-ALIGN: right">8.00</TD><TD><<< using cell properties formula</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD style="TEXT-ALIGN: right">8.43</TD><TD><<< using suggested UDF</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=CELL("width",A1)</TD></TR><TR><TD>B3</TD><TD>=cellwidth(A1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Legacy 32120]

Hi Bill,

Thanks for the code.

I'm only using Excel to create chronological tables of ancient history. Excel functions & formulas haven't been required until now, so pardon if I ask you to explain how to:
>Set a user defined function up in a standard module then use it...

I know about using fills & recording/editing simple macros.

With thanks,

Max

 

[Legacy 32120]

Hello tnazirov,

Thanks for your post.

I need to know precise widths and Bill has provided an answer.

Cheers mate!

Max

 

[Bill Rockenbach]

copy the function code to a standard module of your workbook, then enter a formula in the worksheet like any other formula except use the name of your function.

 

[Legacy 32120]

Thanks muchly Bill!

Can something be added to make it show the widths of a range
and then total the figures?

Max

 

[tnazirov]

Hello Makka,

Column width of the cell rounded off to an integer.

Hello tnazirov,

Thanks for your post.

I need to know precise widths and Bill has provided an answer.

Cheers mate!

Max

 

[Legacy 32120]

Hello Makka,

Column width of the cell rounded off to an integer.

Hi tnazirov,

Sorry, can you give an example of what you mean please?

Max

 

[tnazirov]

Hi makka,

Unfortunately I cannot make attachment, however I hope, that from an example below all will be clear:

<TABLE style="WIDTH: 319pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=425><COLGROUP><COL style="WIDTH: 151pt; mso-width-source: userset; mso-width-alt: 7350" width=201><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" width=23><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 548" width=15><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 13pt; mso-width-source: userset; mso-width-alt: 621" width=17><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 151pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=17 width=201>Data</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 17pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=23>qw</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 23pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2178034 class=xl65 width=31>wert</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 11pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=15>tt</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 11pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=14 align=right>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 21pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=28 align=right>778</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 23pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=31>dfgh</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 13pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: white 1pt solid" class=xl66 width=17>df</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=65>asdfasdfaf</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 17pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=23></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 23pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=31></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 11pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=15></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 11pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=14></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 21pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=28></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 23pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=31></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 13pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: white 1pt solid" class=xl66 width=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=15>Column width (integer figures)</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>3


</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2178034 align=right>4


</TD>


<TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>2


</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2178034 align=right>1


</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2178034 align=right>3


</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2178034 align=right>4


</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2178034 align=right>5


</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2178034 align=right>12


</TD>
</TR></TBODY></TABLE>

Regards.