Column Width in VBA

[nrodrigues23]

I am trying to create a button through VBA Code that allows me to autofit every column in a worksheet based only on the range of cells that has numbers in it (Ie. prevents autofitting based on headers). I found a video online that gives me the following code but it needs to be modified in order to give me what I want.

Private Sub Worksheet_Change(ByVal Target As Range)
Cells.EntireColumn.AutoFit
For i = 1 To ActiveSheet.UsedRange.Columns.Count
Columns(i).ColumnWidth = Columns(i).ColumnWidth + 1
Next i
End Sub

I am new to writing code but this is what I am looking to do.
1. Create a button at the top of the screen to press when I want to adjust the width of columns. (I know how to assign a macro to a button)
2. I want to autofit the column and subtract from the autofitted width (my boss likes things tight on the page)
3. I want to autofit based only on the numbers in the columns and not the header (I have some merged headers)

Note: the code above seems to be adding "1" width to the column every time something changes on the page. I would like it to simply revert to autofit - 1 when I press the button.

Any help would be help me save a ton of time...

 

[BlueHornet]

Welcome to the board.

Here's a relatively quick way to get what you want, but it will require a certain amount of setup on your part.

The problem with what you say you want and the code you have is that "EntireColumn.AutoFit" - that's always and ever going to include the contents of every cell, including titles and headers. Always.

If you set up a data range on your sheet where you want this to work, and if you uniformly (on every sheet where the macro needs to work) call it by the same name (and that means making it a "Workbook" level name, and not a Worksheet name), then it's possible that you could also change the code to say "MyDataRange.Columns.AutoFit" (I'm spitballing here; I haven't tested this) and have the AutoFit done only over the pre-selected range/s.

But even though I often have similar objectives in my own worksheets, I'm not going to pursue this. It's already trivially easy to AutoFit one or more columns in a group, and then to groupwise select columns and make them all the same width, or do the same thing individually, and at that point ignore titles, column headers, notes, etc. This code actually seems to be more trouble than it would be worth - to me - but I wish you luck with it.

 

[nrodrigues23]

Thanks for the idea. The reason I am looking to add this to my file is because I have a variety of columns. The sheet is linked to other tabs in the workbook and I have used a choose function for the cells to automatically update based on month, month to date and year to date data. Although this saves time, I am constantly toggling back and forth between periods, which creates a ton of time spent on manually adjusting columns in order to make them "powerpoint friendly."