change column width based on concatenated cells

[Guest]

I concatenate up to 17 cells (Sheet1!B6 thru Sheet1!R6) into one large,
merged cell (Sheet2!R96). I want my macro to change the width of the merged
cell (Sheet2!R96) depending upon how much text is concatenated. The merged
cell could have as little as 20 characters and as many as 60 characters of
text. Is there a way for a macro to "count" the number of characters in a
cell or, some other way, to determine the amount of text within that cell and
then adjust the width of the cell accordingly?

 

[KCarhart]

Hi crimsonkng

You can say

x = Len(ActiveCell.Value)
ActiveCell.ColumnWidth = x

And possibly work out a conversion factor (once) if 5 characters does
not necessarily look the best in five width-units. You might want to
multiply x by 1/2, 1/4 etc.

 

[Guest]

Awesome. I'll try it. Thanks.