Auto adjust column widths in Excel while excluding certain cells/rows

Question

I have looked to find related questions multiple times, but I have never found a similar question. Everyone is always wanting to know how to adjust column widths, that's easy, I have much more nuanced question.

I love auto adjust, makes clean looking tables and makes sure everything is visible (aka.; ctrl-a, double click up top between columns), but if you have one cell with a long piece of text (like notes at the bottom of a table), it makes that column like 400 pixels wide to capture the entire text, when I just wanted it to update per my column heading or table data. I would like to be able to flag this text cell to be ignored by excel for auto adjustment.

If you are OK with it being Centered, then you can set that cells alignment (and 1 cell to the right's) to "Center Across Selection" and it will be ignored. But, like my example above, I often want this text justified left or right, not allowing C.A.S. to work.

Has anyone been able to accomplish this?

EDIT: After over a decade of searching, I've found the answer. It is to use Center Across Selection as described above, but to use Custom Number Formats to auto-pad the text with spaces for the desired justification. I put together an image below to help explain this graphically.

Copy-able Custom Number Formats here as well (They only need to applied to the left-most cell with the text, all affected cells need to be Center Across Selection'd):

[left justified, remove quotes, note end-space] "General* ;General* ;General* ;General* "

[right justified, remove quotes] "* General;* General;* General;* General"

I found that, in Office 2016 at least, they have implemented a command that is basically exactly what I wanted. Home -> Cells -> Format -> AutoFitColumnWidths (Alt->H O I). You can select certain rows or some cells, etc. and use that command to auto adjust the highlight columns by only the highlighted cells. — kindlin, Nov 22 '17 at 17:34

score 1 · Answer 1 · answered Dec 06 '19 at 20:34

1

Not sure if it's by design, but in my experience autosizing ignores any cells that have wrap text turned on. Thus, maybe consider turning on text wrapping for the range you want ignored when autosizing.

answered Dec 06 '19 at 20:34

Brian

11
1

This works, and in many cases it makes sense to enable text wrapping in such cells. Thanks! – NeatNit May 31 '20 at 11:10
This does not work for me. When shrinking the width of a column, text in cell with wrapping enabled are being considered and the width only shrinks to the next whitespace so that no new word is wrapped to the next line. This is in the Microsoft 365 version 2108, build 14326.20784 Click-to-Run. – Beat Nideröst Feb 22 '22 at 06:38

score 0 · Answer 2 · answered Oct 15 '16 at 02:11

If you're okay with a VBA solution, you can write a custom routine to do this. In this example, if you pass the range you want to "Autofit Headers Only," it will essentially copy the header to a clean cell, run autofit, and then apply that autofitted width to your column:

Sub AutoFitHeader(HeaderRow As Range)

  Dim col As Range
  Dim ws As Worksheet
  Set ws = ActiveWorkbook.Worksheets.Add
  ws.Visible = xlSheetHidden

  Application.DisplayAlerts = False

  For Each col In HeaderRow
    ws.Range("A1").Value = col.Value
    ws.Columns("A").AutoFit

    col.EntireColumn.ColumnWidth = ws.Columns("A").ColumnWidth
  Next col

  ws.Delete

  Application.DisplayAlerts = True

End Sub

And then call it as such:

AutoFitHeader Range("A1:H1")

This may be nearly the best possible solution, but I have a couple questions. Could this be extended to a 2d range, so I would highlight my whole table and not just a single row? Or, honestly, the inverse would be preferred, "Autofit to all data that isn't in cells X Y or Z" (or maybe, range A13:H20)? And then, how do you actually run this? Do you call it like a UDF in a cell? Or is there a VBA run-space where you need to type the VBA looking command "AutoFitHeader Range("X:Z')"? — kindlin, Jan 13 '17 at 22:02

score 0 · Accepted Answer · answered Oct 31 '22 at 06:10

I was able to find a solution to this based on using Center Across Selection (TLDR: See the edit to the original post). The beauty of CAS is that for the purposes of any Auto-Adjustment of Column Widths, Excel ignores any text typed in a cell using that CAS text alignment as long as there is also at least 1 blank cell to the right of your CAS text that is also using CAS (so it can't work on just 1 cell). This only works for text you WANT centered though, for anything else, you need to modify this method.

For right justify, enter [your text] into the left-most cell to be combined into the justified region, CAS the region (under Formatting, Alignment, Horizontal, Center Across Selection), select the cell with text and enter as a Custom Number Format (Formatting, Number, Custom):

[* General;* General;* General;* General]

This uses the standard General formatting for each of the positive;negative;zero;text values but takes each one and repeats ("*") a character (" ") to pad it before the value to fill up the entire selection.

For left justify, it's exactly the same as above, except the Custom Number Format is:

General* ;General* ;General* ;General*

Noting the final space after * .

See an example of this in graphic/Excel formatting:

Addendum to the CAS & Custom Format answer: If, on following the instructions to the letter, your long text is displayed as ####### across the two cells ["set that cells alignment (and 1 cell to the right's) "] simply increase the number of cells you apply the CAS to further to the right until the text is displayed correctly. This worked for me in Office 365. — Chris van Zyl, Feb 13 '23 at 13:43

Auto adjust column widths in Excel while excluding certain cells/rows

3 Answers3