Dynamically setting the Excel column size

Navigation
3

I'm working with the generation of spreadsheets in EXCEL, and for this I use the standard api of the framework. 

Imports Microsoft.Office.Interop.Excel

However, when I have a column with very long text, the line is broken with each word or according to the size of the title of the header. I wonder how to make this more visually pleasing. Is there any way to dynamically assign the width of the excel columns so that they are visibly more pleasing?

I'm trying to use autofit, but it does not seem to have the desired effect. 

    xlWorkSheet.Cells.Rows.AutoFit()
    xlWorkSheet.Cells.Columns.AutoFit()

example http://i57.tinypic.com/dyvm3b.png

    
asked by anonymous 23.04.2014 / 14:01

4 answers

3

Use the following command to automatically adjust the column width according to the text that fills your lines: 

Columns("C:C").EntireColumn.AutoFit

For example, I have adjusted the size of the column C only, however you can set a range of all columns that you want to auto adjust.

    
23.04.2014 / 14:30
2

In similar code, I was able to fine-tune the cells of the header -range ("A1: Z1"). Before performing the autofit command, you must configure the cells for automatic line feed (.wrapText), and the columns must have their width adjusted to a value less than the smallest final width. In the example below, the smallest final width, among all the columns, was greater than 12. Take the example: 

    Plan2Dados.Rows("1:1").WrapText = True
    Plan2Dados.Columns("A:Z").ColumnWidth = 12
    Plan2Dados.Columns("A:Z").AutoFit
    
24.02.2017 / 04:23
1

try: 

Worksheets("Plan1").Columns.AutoFit

or

'A:I can be overwritten by the range you want. 

Worksheets("Plan1").Columns("A:I").AutoFit
    
02.08.2016 / 22:08
-1

Good morning, I'm not answering the question because of all of them, the only answer that worked for me was that of Roberto Santos. 

 Plan1.Rows("1:1").WrapText = True
 Plan1.Columns("A:Z").ColumnWidth = 12
 Plan1.Columns("A:Z").AutoFit

I just adjusted to my 'Plan1', tried all possible and only this one worked, congratulations Roberto.

    
27.11.2018 / 14:31
InnoDB and MyISAM generating Table already exists error Iteration gives ConcurrentModificationException error when adding more than one button