Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Autofit row/column height | Excel Discussion (Misc queries) | |||
Column Autofit | Excel Discussion (Misc queries) | |||
What DDE command may I send to Excel to autofit a column? | Excel Discussion (Misc queries) | |||
Get Autofit in Excel to format just part of a column? | Excel Programming | |||
Column Autofit Problem | Excel Programming |