Very often, my “customers” asked me to retrieve records from database and pass it to them as an Excel Document. Previously, I need to manually set the width and wrap each of the columns.

I found it tedious and time wasting (the manual formatting, not the user’s request :D)

This macro below will automatically set all columns to auto-fit and wrap those columns that are too wide.

To add macro to your Excel, follow these steps:

1. Click Tools -> Macro -> Record New Macro
2. Give the Macro a meaningful name and description. The shortcut key is optional. If you want to make the Macro available in any Excel in your PC, store the macro in “Personal Macro Workbook”. Click OK to continue.
   
3. A small floating window will appear. Click the ‘Stop Recording’ button.
4. Click Tools -> Macro -> Visual Basic Editor (or press Alt-F11)
5. Complete the code as shown below
6. Save it. The macro now will be run whenever you press the shortcut key (if you defined it), or you could find the macro through clicking Tools -> Macro -> Macros (or by pressing Alt-F8)

Sub FormatQueryResult()
'
' FormatQueryResult Macro
' To make the Database query result less irritating to the eyes
'
    Dim i As Integer 'counter
    SendKeys "^a", True 'Send Ctrl-A to select All
        
    'Make the column auto fit
    For i = 1 To Selection.Columns.Count
        ActiveSheet.Columns(i).AutoFit
    Next
    
    'Wrap columns that are too wide
    For i = 1 To Selection.Columns.Count
       If ActiveSheet.Columns(i).Width >= 250 Then
            ActiveSheet.Columns(i).ColumnWidth = 65
            ActiveSheet.Columns(i).WrapText = True
        End If
    Next
End Sub

Incoming Search

ms excel, vba