Toggle all Power query Columns Auto-Fit

When loading data into an Excel Table using PowerQuery, the Table columns may automatically resize each time the query is refreshed. This can affect the readability of the data.

You can easily change its setting.

Here is a LinkedIn post from Mark Proctor explaining all the steps.

I just wanted to add an automatic way to do this for all the Power Query table for active workbook.

Just copy paste below code in a module and you can run this to toggle the feature.

Public Sub ToggleAllQueryColumnsAutoFit()

    Dim FromBook As Workbook
    Set FromBook = ActiveWorkbook

    On Error Resume Next
    Dim CurrentSheet As Worksheet
    For Each CurrentSheet In FromBook.Worksheets

        Dim CurrentTable As ListObject
        For Each CurrentTable In CurrentSheet.ListObjects

            If Not CurrentTable.QueryTable Is Nothing Then
                CurrentTable.QueryTable.AdjustColumnWidth = Not (CurrentTable.QueryTable.AdjustColumnWidth)
            End If
        Next CurrentTable

    Next CurrentSheet
    On Error GoTo 0

End Sub

Some idea on how to use this code:

1. You can have this code in your Personal Macro Workbook and use it from that.
2. You can have this code in your Add-In like me and add a ribbon button for that.