4

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.

  • I don't think there would be an option for that. – Máté Juhász Jun 18 '18 at 3:50
  • Ctrl + A... Alt > H > O > I. Takes 2 seconds but accomplishes what you want. Just make sure you're not in a table when you Ctrl + A. – Jacob Jun 18 '18 at 5:04
  • 1
    @Jacob That is a good shortcut to keep in mind, but that one actually accomplishes the exact opposite of what I want. Edit: Sloppy fingers sent before finish typing – David Jun 18 '18 at 5:07
  • Why don't you turn off Auto Fit facility for the Table. – Rajesh S Jun 18 '18 at 9:01
2

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.

1

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.
0

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
  • I also noticed the '¡' where it's common practice to use the letter 'i', but I think it's sort of his signature for the code he puts up here on SE. I find it weird, however, that it didn't run for you due to the exclamation mark because as far as I know any ASCII character above 128 that is printable is fair game for variables in VB6 and VBA. Now regarding the way the function handles the styles, clearing the formatting was actually something that I wanted, but your version may be helpful to someone else who comes across this. – David Oct 6 '18 at 4:22
  • It belongs to the extended ASCII. According to the documents, the variable name only accepts alphabetic characters, decimal digits, and underscores. Didn't you get an error when you pasted/ran the codes? I'm using Excel 2016 and VBA 7.1. It did throw an error when I pasted the original codes. – masterton Oct 6 '18 at 10:47
  • Apart from the above, the variable name must begin with an alphabetic character or an underscore. If it begins with an underscore, it must contain at least one alphabetic character or decimal digit. It can't be more than 1023 characters long. – masterton Oct 6 '18 at 10:47
  • I just re-ran his code in a fresh workbook and also made a dummy public function with a single-character variables using random ASCII characters from the extended set for good measure and it ran both just fine. The full expression is this Foo = • ^ ¡ * ˆ - × P.S. also, I correct myself of what I said earlier as even non printable characters can be used as identifiers. – David Oct 8 '18 at 1:25
  • 1
    That's weird. We are both using Excel 2016. Anyway the code will fail to run if I use symbols except underscores. Avoid using them as it is not compatible with all cases. Here is the document: docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/… – masterton Oct 10 '18 at 20:08
0

Go to Table Tools > Design Select Properties Uncheck 'Adjust column width' Click OK

  • Please comment on how this solves OP's problem. – vonbrand Mar 10 '20 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.