Problems adjusting column width

[sean_mufc]

Hello all,

Just a quick question does anyone know of a way to programmatically adjust
the column with to ensure that the text within a cell is entirely visible.

I have tried

xlSheet.Columns.ShrinkToFit = true;

but this doesnt seem to work.
I have also tried doing it column by column using a range and a foreach loop
but this has the same result.

foreach (string str in arrCols)
{
xlRange1 = xlSheet.get_Range(str + 1, str + 73);
xlRange1.Columns.ShrinkToFit = true;
}

Ive also tried AutoFit() but again not what I need. This just widens all the
col widths to a standard area thats too large for what I need. I just need
the col width to be wide enough to show all the text and thats it, nothing
longer

Any suggestions??

Cheers,
Sean

 

[Don Guillett]

Look in the vba help index for AUTOFIT

 

[Gary''s Student]

If we are interested in a given column, say column D:

Columns("D").EntireColumn.AutoFit

If we are interested in more than one column, then just loop.

 

[sean_mufc]

I did, could not find any help there either

Look in the vba help index for AUTOFIT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[sean_mufc]

We're actually dealing with several ranges. The problem is AutoFit() does
re-size width but its over twice as wide as needed. I'm really looking for
another way to resize (not .ShrinkToFit = true as this doesnt work either).
I've tried resizing using ranges and using either worksheet instances but
they dont work either

 

[Don Guillett]

Sub docolwidth()
lr = Cells(Rows.Count, "d").End(xlUp).Row
For Each c In Range("d1:d" & lr)
c.Value = Trim(c)
Next c
Columns("d").AutoFit
End Sub

 

[Mike Middleton]

sean_mufc -

We're actually dealing with several ranges. The problem is AutoFit() does
re-size width but its over twice as wide as needed. <

Suggestion: Instead of using AutoFit on an entire column, use it only for a
specified range of cells. For example,
Range(Cells(12,3),Cells(15,7)).Columns.AutoFit

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel