Automatically Auto-Fit Visible Columns Only

PY_ · May 8th 2008

In the code below, I found out how to make the column widths automatically adjust on a worksheet based on the text entered.

This code works great but it also reveals the hidden columns on the worksheet. Can i prevent it from doing that?

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me.Cells
        .Columns.AutoFit
    End With
End Sub

Found here:
http://www.ozgrid.com/forum/showthread.php?t=59876

Simon Lloyd · May 8th 2008

Re: Auto-resize Columns Without Unhiding All Columns

Code

Private Sub Worksheet_Change(ByVal Target As  Range) 
Application.ScreenUpdating=False
    With Me.Cells 
        .Columns.AutoFit 
    End With 
Range("A:C").EntireColumn.Hidden = True' where coulmns A to C are hidden
Application.ScreenUpdating=True
End Sub

Dave Hawley · May 9th 2008

Re: Auto-resize Columns Without Unhiding All Columns

Code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next 'Remove to debug
    Me.Columns.SpecialCells(xlCellTypeVisible).Columns.AutoFit
On Error GoTo 0
End Sub

PY_ · May 10th 2008

Re: Automatically Auto-Fit Visible Columns Only

Thank you both for the help!

Automatically Auto-Fit Visible Columns Only

Participate now!

Share