Excel column autofit

[J. M. De Moor]

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<LF>Text Foobar<LF>Text 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

 

[Nick Hodge]

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
(e-mail address removed)
www.nickhodge.co.uk

 

[J. M. De Moor]

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

 

[Nick Hodge]

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk