Resize columns, excluding hidden rows

[bfoxworthy]

Is there any way to re-size all the columns on a worksheet to the widest visible cell in the column? Right now Excel re-sizes columns including hidden values.

Should be a simple solution?

 

[Rick Rothstein]

Something like this maybe...

Dim A As Range
For Each A In Columns.SpecialCells(xlCellTypeVisible).Areas
  A.EntireColumn.AutoFit
Next

 

[bfoxworthy]

Sorry, how exactly do i integrate that?

 

[Rick Rothstein]

Sorry, how exactly do i integrate that?

No, I'm sorry... for some reason, I thought you had an existing macro and the code I posted was meant to be integrated into it at whatever point you wanted to resize the column widths. Let's convert what I posted to a stand-alone macro so that you can call it whenever you want. First, from any worksheet, press ALT+F11. This will open the VB editor. Once there, click Insert/Module on its menu bar. This will open up a code window; copy/paste the following code into it...

Sub AutoFitVisibleColumnsOnly()
  Dim A As Range
  For Each A In Columns.SpecialCells(xlCellTypeVisible).Areas
    A.EntireColumn.AutoFit
  Next
End Sub

Now, to use this macro, go to the worksheet with the (visible) columns you want to autofit and press ALT+F8. This will open a dialog box... select AutoFitVisibleColumnsOnly from the list and then click the Run button.

 

[Rick Rothstein]

Just to follow up on my last message, you can do what you asked without using code. Press CTRL+G, click the "Special" button, select the "Visible cells only" option button and then click the "OK" button. Now, do your Columns AutoFit (the method depends on your version of Excel, but since you asked, I assume you know how to do this)... only the visibles will be resized.