Autofit to fully display header in a filtered column

This has been bugging me for years, and I want to know if there's a fix:

I have hundreds of columns, each with a colomn header name. Autofilter is applied. After refreshing the data, I want to autofit the columns, but autofit sometimes obscures the headers. See the attached picture for an example. Column C title is "Recd By BA", and column J title is "PAC". How do I autofit and get the full name of the column to display?

Any help is appreciated. I'm not averse to using a VBA solution if necessary, but it seems to me this is something Excel should be able to do natively.

Yes, that's normally the way I do my manual autofit. The example I posted is merely one example; this happens to me quite often, which is why I'm finally annoyed enought to post about it. The only way I've found to achieve the results I need is to alter the problem Headers by adding 3 or 4 spaces at the end. Then when I apply the autofilter my whole true heading shows. But that's an exhausting workaround when there are hundreds of columns.

My example is attached. As per instructions I've clicked on the triangle just above the row numbers and to the left of the column letters (aka the arrow in the top left corner), which selected the whole sheet. I then double clicked on the column divider between B and C, which autofit all of my columns. AFTER the autofiting, results are as in the attachment, and as stated in my original post. Note columns C and J, which still persist as displaying with part of the header hidden.

Thanks for the insight. Okay, so now I can understand why it's behaving that way: having them center-aligned is what's messing up those columns. So, if I align left those work fine. That's well and good for a limited number of columns. However many of the workbooks I work with have tons of columns. If I left align the headers to get the titles to show properly for my problem columns, if I have columns containing long strings of data, my headers are going to be way to the left side, instead of centered over my data.

I want both options: I want my headings centered, so for wide columns the headings are easily associated with the data, and I want the header rows to display fully without having to do a special left alignment for them.

Any solution ideas?

Okay, this is weird: I decided to try my own solution, by inserting 3 spaces after each header, using my Cells_Append macro (code is below). I then centered the headers, and autofitted. They all fit beautifully. Then I decided to undo it by using my Cells_Clean macro (code is below). After cleaning, and after Autofitting again, leaving the headers centered, J and C STILL showed correctly. I verified that there were no longer any spaces after them. I then Left aligned column J, and autofitted, and the column shrank in size. I then recentered J, and autofitted again, and the column increased in width to fully display the header. So, why is the autofit procedure working now, when no real change has been done to the cells?

Please Login or Register  to view this content.

You're probably right. Seems like center alignment should work in all cases. Thanks so much for all the help.