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

 

[Bill Rockenbach]

Option Explicit

Sub CellWidth()
    Dim c As Range
    For Each c In Range("A1:Z1")   '<<< change to your range of columns
        '
        'use one (and only one) of the folowing statements
        c = c.ColumnWidth           'actual columns width including decimals
        c = Round(c.ColumnWidth, 0)    'column width rounded to integer
        c = Int(c.ColumnWidth)     'integer portion of column widthe
    Next c
End Sub

 

[Legacy 32120]

Thanks Bill,

That works nicely

A minor point, the figure in column C comes out underlined & in blue like a hyperlink.

Now, what to add to autocalculate the total width(s)?

Cheers,

Max

 

[Legacy 32120]

Hi again tnazirov,

Got what you mean thanks. No, not integer but decimal figures.

Cheerio,

Max

 

[tnazirov]

Hi again tnazirov,

Got what you mean thanks. No, not integer but decimal figures.

Cheerio,

Max

Hi Makka,
By definition of function we receive integers. I, for example, receive integers.
But if you receive decimal numbers is even better.
Good luck.

 

[Bill Rockenbach]

To get total column width of range entered

Option Explicit

Sub CellWidth()
    Dim c As Range
    Dim TotalColWidth As Double
    For Each c In Range("A1:Z1")   '<<< change to your range of columns
        '
        c = c.ColumnWidth           'actual columns width including deimals
        TotalColWidth = TotalColWidth + c
    Next c
    Range("A2") = TotalColWidth
End Sub

A minor point, the figure in column C comes out underlined & in blue like a hyperlink.

Must be a formating problem. Clear All (not Clear Contents) the cell in question and try again.

 

[Legacy 32120]

Great Bill!

First go made a few figures come out in bold.
Used 'Clear All' as you said and that fixed that.

Much obliged to you mate!

All the best...

Max

P.S. Care to have a go with my post - 'Create index of sheet data'?