Dynamically set the Excel column size

Asked

Viewed 11,775 times

3

I am working with the generation of spreadsheets in EXCEL, and for this I use the framework’s default api.

Imports Microsoft.Office.Interop.Excel

However, when I have a column with very long text, the line is broken to each word or according to the size of the header. I’d like to know how to make this more visually pleasurable. Is there any way to dynamically assign the width of excel columns, so that they are noticeably more pleasant?

I’m trying to use autofit, but it doesn’t seem to have the desired effect.

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

exemplo

excel vba

4 answers

3


Use the following command to automatically adjust the column width according to the texts that fill your lines:

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

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

  • I already tried this command, but it did not work as I wanted. I managed to "solve" the problem by increasing the column title.

  • @Cleitonribeiro this command only does not auto fit columns that have merged cells, would that be your case? I suggest you merge as little as possible, maybe just the title

2

In similar code, I was able to adjust the -range header cells ("A1:Z1") - correctly. Before performing the autofit command, you must configure the cells for automatic line feeding (.wrapText), and the columns must have their width adjusted to a value smaller than the smallest final width. In the example below, the smallest final width of all 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

1

try:

Worksheets("Plan1").Columns.AutoFit

or

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

Worksheets("Plan1").Columns("A:I").AutoFit

-1

Good morning guys, I’m not answering the question because among all, the only answer that worked for me was Roberto Santos.

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

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

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.