Getting column width sizes

  • Thread starter Thread starter Tom Walat
  • Start date Start date
T

Tom Walat

Using Excel 2000.
I hope I can explain this well enough, so here goes.

I have a 4 column chart:
!candidate!prec. 1!prec. 2!total!
!Joe Smith!10!10!20!
!Jane Doe!15!15!30

Someone posted the following code which calculates the width
of a selected cell in points:
Sub CalcWidth()
W = Selection.Width
MsgBox W
End Sub

What I'm trying to do:
I'm converting worksheets into charts for an editorial layout
system, but I just need a way to easily get those numbers. I need the
macro to go to each cell in the selected range, get the width and
insert those numbers in the A1 cell, separated by commas. I'd also
like the number of columns inserted as well. I'd like them in this
format:
t4v 170,32,32,32
... where, using my chart example, the 4 is the number of
columns and the other numbers are the column widths in points.

One problem:
The results from the macro above are not whole numbers, which
I need. Rounded up is fine.

Any help would be greatly appreciated. Thanks,

- Tom
twalat@_NOSPAM_enterprisenews.com
(Remove _NOSPAM_ for e-mail replies)
 
See if this helps

Sub columninfo()
cols = "t" & Selection.Columns.Count & "v "
For Each c In Selection
mystr = mystr & c.Width & ","
Next
MsgBox cols & mystr
End Sub
 
Here's a simple function to do what you need to do:

Add this code in a module in your workbook.

Public Function Cwidth(r As Range) As String
c = r.Columns.Count
Cwidth = "t" & Trim(Str(c)) & "v "
For i = 1 To c
Cwidth = Cwidth & Trim(Str(Int(r(i).ColumnWidth))) & ","
Next
Cwidth = Left(Cwidth, Len(Cwidth) - 1)
End Function

Now, you can type =cwidth(F3:H3) in A1 and it will give
you all you need.
 
to remove the .xxx
mystr = mystr & Int(c.Width) & ","
Click to expand...

Don:
Thank you!
Is there any way to remove the trailing comma?
Other than that, it's perfect.
- Tom
twalat@_nospam_enterprisenews.com
(Remove _nospam_ for e-mail replies)
 
Nice function. To get width instead of columnwidth just change.
Cwidth = Cwidth & Trim(Str(Int(r(i).ColumnWidth))) & ","
Click to expand...
Cwidth = Cwidth & Trim(Str(Int(r(i).width))) & ","
 
Back
Top