Excel column autofit
 

I have an Access application that pushes a recordset to an Excel
spreadsheet. The Access program calls the Excel function to autofit the
columns:

wks.Columns("A:Q").AutoFit

This works OK, except that one of the columns has a line feed characters in
it. Is there a way to autofit to the width of the column to the line feed
of the longest line in the set, e.g, If the cell has:

Text Foo<LFText Foobar<LFText Bar

The cell should appear like this:

Text Foo
Text Foobar
Text Bar

with the autofit adjusted to the width of the 2nd line. Is this possible
with Excel VBA?

Joe

Excel column autofit
 

If I understand correctly, you should be able to wrap the data first and
then autofit, like so

Sub Test()
Columns("C:C").WrapText = True
Columns("A:Q").AutoFit
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"J. M. De Moor" wrote in message
...
I have an Access application that pushes a recordset to an Excel
spreadsheet. The Access program calls the Excel function to autofit the
columns:

wks.Columns("A:Q").AutoFit

This works OK, except that one of the columns has a line feed characters
in it. Is there a way to autofit to the width of the column to the line
feed of the longest line in the set, e.g, If the cell has:

Text Foo<LFText Foobar<LFText Bar

The cell should appear like this:

Text Foo
Text Foobar
Text Bar

with the autofit adjusted to the width of the 2nd line. Is this possible
with Excel VBA?

Joe

Excel column autofit
 

Hey Nick

Thanks for your post.


Sub Test()
Columns("C:C").WrapText = True
Columns("A:Q").AutoFit
End Sub


Unfortunately this doesn't work because the text wraps on word break, where
I would like to have it wrap only on the line feed. Also I can't predict
which column will have the line feeds. (The output is from an ad hoc query
in SQL Server.) If you are curious, the way I ended up working around it is
to make the default column width on the template wider than I expect a
single line (to the line feed) to be. Then the Autofit trims it nicely.

Not elegant, but it is working...

Joe

Excel column autofit
 

As long as things work, that's fine...I won't tell anyone ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"J. M. De Moor" wrote in message
...
Hey Nick

Thanks for your post.


Sub Test()
Columns("C:C").WrapText = True
Columns("A:Q").AutoFit
End Sub


Unfortunately this doesn't work because the text wraps on word break,
where I would like to have it wrap only on the line feed. Also I can't
predict which column will have the line feeds. (The output is from an ad
hoc query in SQL Server.) If you are curious, the way I ended up working
around it is to make the default column width on the template wider than I
expect a single line (to the line feed) to be. Then the Autofit trims it
nicely.

Not elegant, but it is working...

Joe