Avoid Cell Resizing When Creating A Table using Excel

Question

I've searched far and wide on the Internet, but all the solutions for the questions I find are on how to keep excel from resizing cells in an already existent table or pivot table. I also searched in the settings under options, but I didn't see any one setting that would fix my issue.

My exact problem is that I have a single worksheet in which I want to create multiple tables that store information about my paychecks by year. I've already did the first table for the first year for which I already sized the columns to make the data easy to read. However, when I select my new headers and click Format as Table, Excel resizes all my columns to fit the new text.

Some of my headers do not quite fit in the columns, but this table is for personal use, and I prefer my headers to have clear names (although longer) to reference them easier later. So I hope someone has a solution to this. It would be really annoying having to manually resize all the columns again.

Answer 1

There are two ways of accomplishing this:

1. The simplest way is to create an empty table first and then set it up the way you want.
2. Use a macro that saves the selection's column widths, formats it as a Table, and then restores the column widths. The macro can be set up to work with a shortcut key or a command button, or it can be added to the ribbon. It is even possible to have it intercept the Format as Table ribbon tool.

For method 2, add the following code to a standard module:

'============================================================================================
' Module     : <any standard module>
' Version    : 0.1.0
' Part       : 1 of 1
' References : N/A
' Source     : https://superuser.com/a/1332155/763880
'============================================================================================
Option Explicit

Public Sub ToggleTable_NoResize()
       Dim ¡ As Long

  Const s_DefaultStyle As String = "TableStyleMedium9" ' Change this for a different default style

  Dim asngColumnWidths() As Single
  ReDim asngColumnWidths(1 To Selection.Columns.Count)
  For ¡ = LBound(asngColumnWidths) To UBound(asngColumnWidths)
    asngColumnWidths(¡) = Selection.Columns(¡).ColumnWidth
  Next ¡
  Application.ScreenUpdating = False
    Dim loNewTable As ListObject
    On Error Resume Next
      Set loNewTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
    On Error GoTo 0
    If loNewTable Is Nothing Then
      Dim loExistingTable As ListObject
      On Error Resume Next
        Set loExistingTable = Selection.ListObject
      On Error GoTo 0
      If Not loExistingTable Is Nothing Then
        loExistingTable.Unlist
      End If
    Else
      loNewTable.TableStyle = s_DefaultStyle
      For ¡ = LBound(asngColumnWidths) To UBound(asngColumnWidths)
        Selection.Columns(¡).ColumnWidth = asngColumnWidths(¡)
      Next ¡
    End If
  Application.ScreenUpdating = True

End Sub

To set it up with a shortcut key:

• Make sure the Developer tab is showing
• Press Alt+L+P+M; select the macro; press Options…; and set the shortcut key

Notes:

The default style of the Table can be changed in the code where indicated.

As an added feature, running the macro again will convert the Table back to a normal range of cells.

Answer 2

If, as in my case, this is an infrequent but vexing annoyance, then you can use this workaround, which effectively caches the column widths. It's in the spirit of that macro, I think, but without the macro.

1. Make a temporary copy: Ctrl+A,   Ctrl+C,   Ctrl+N,   Ctrl+Home,   Ctrl+V.
2. Create the table, or do whatever else causes Excel to mess up your column widths.
3. Restore only the widths: Alt+Tab,   Ctrl+A,   Ctrl+C,   Alt+Tab,   Ctrl+Home,   Ctrl+Alt+V,   W,   Enter,   Enter.

Suggestions, if you're making a macro of this:

1. In some contexts, Ctrl+A won't select the entire sheet, even if repeated. Instead use: Ctrl+Home,   Shift+Ctrl+End.
2. To avoid making a copy of your data (because you might edit the wrong copy, or because it's sensitive), replace the first Ctrl+V, with: Ctrl+Alt+V,   W,   Enter.

Answer 3

Full credits to the original marco created by robinCTS.

However, there are slight issues in the macro. It has a special character "¡" (inverted exclamation mark) in the codes. It throws an error when you run it. I made slight modifications to fix it.

The default behavior is changed too. It preserves the current formatting/style of the table and will not apply any formatting/style.

Running the macro again will convert a table back to a data range.

Option Explicit

'A slight modification of the code from https://superuser.com/a/1332155/763880    
Public Sub ToggleTable_NoResize()
  Dim var As Long

  Const s_DefaultStyle As String = "" 'Change this if you want a different default style

  Dim asngColumnWidths() As Single
  ReDim asngColumnWidths(1 To Selection.Columns.Count)
  For var = LBound(asngColumnWidths) To UBound(asngColumnWidths)
    asngColumnWidths(var) = Selection.Columns(var).ColumnWidth
  Next var
  Application.ScreenUpdating = False
    Dim loNewTable As ListObject
    On Error Resume Next
      Set loNewTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
    On Error GoTo 0
    If loNewTable Is Nothing Then
      Dim loExistingTable As ListObject
      On Error Resume Next
        Set loExistingTable = Selection.ListObject
      On Error GoTo 0
      If Not loExistingTable Is Nothing Then
        loExistingTable.Unlist
      End If
    Else
      loNewTable.TableStyle = s_DefaultStyle
      For var = LBound(asngColumnWidths) To UBound(asngColumnWidths)
        Selection.Columns(var).ColumnWidth = asngColumnWidths(var)
      Next var
    End If
  Application.ScreenUpdating = True

End Sub