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

2
votes

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?

2
excelcellsexceptcolumn-width
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
Thanks - really useful! :) - willsaunders

2 Answers

1
votes

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.

0
votes

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")