Excel: how to auto-select columns at startup?

Question

Is there a way to auto-validate all columns in the opening sheet that contain data? I am looking for a solution that does not depend on changes to a specific excel file, but which works on every excel file and runs when excel starts.

Answer 1

You can use the object module that receives the Application Event (more about the Chip Pearson website ).

This is the code you must copy-paste into the ThisWorkbookfile module PERSONAL.XLSB:

Option Explicit

Private WithEvents App As Application

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    'Statement to show that it works
    MsgBox "Opened Workbook: " & Wb.Name
    'Statement to autofit columns
    ActiveSheet.UsedRange.Columns.AutoFit
End Sub

You can also add a loop over the sheets of the book.