How to increase the textarea of columns through Excel VBA

Question

I'm retrieving data from a table through VBA.The select query gives output correctly.One of the columns that I select has text description which is multiline.So when I get my rows,I want to increase the textaread of my spreadsheet columns dynamically according to the results I'm getting.How can I do this

Answer 1

You can use the AutoFit method on the columns containing your query result, for example

Columns("A:G").EntireColumn.AutoFit ' hardcoded ... works but isn't nice

If you have a range defined for your query result, use this code fragment:

Dim QRslt As Range, QCol As Range

    '....
    For Each QCol In QRslt.Columns
        QCol.AutoFit
    Next
    '....

If you want to limit the column width to a certain maximum size you may as an alternative set the .WrapText property of the cells to =True

Hope that helps.

Good luck - MikeD