Link to home
Start Free TrialLog in
Avatar of NewBieSteffie
NewBieSteffieFlag for Philippines

asked on

how can I adjust width without expanding the cells?

Hi Experts!

I want to adjust the width of the all the columns from C2 up to the last column
these are my code

Dim LastColumn As Long
LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
Columns("C2", LastColumn).EntireColumn.AutoFit

My problem is
  A.   I have a run -time error on this part
  Columns("C2", LastColumn).EntireColumn.AutoFit
  B.   I have a group cells and I want it to remain it group as I autofit all the cells
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NewBieSteffie

ASKER

thank you for this @ HainKurt  but How can I auto-width it without expanding the group cells that I made?
Avatar of Ryan Chong
B.   I have a group cells and I want it to remain it group as I autofit all the cells
how's your group cells look like?
I made my group like the image below
 User generated image
the problem is when I put the code the group will expand.I want to remain it collapse
Hi Try below:
Assuming your sheet name is Sheet1, change accordingly.
Sub AutofitColumns()
Dim Ws As Worksheet
Dim LCol As Long
Dim Rng As Range, Col As Range
Application.ScreenUpdating = False
Set Ws = Worksheets("Sheet1")
LCol = Ws.Cells(2, Columns.Count).End(xlToLeft).Column
Set Rng = Ws.Range(Ws.Cells(2, 3), Ws.Cells(2, LCol))
For Each Col In Rng.EntireColumn
    If Col.Hidden = False Then
        Col.EntireColumn.AutoFit
    End If
Next Col
Application.ScreenUpdating = True
End Sub

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try this code:

Sub AutoFit()
    For Each Column In ActiveSheet.UsedRange.Columns
        Count = Count + 1
        If Count > 2 Then Column.EntireColumn.AutoFit
    Next Column
    ActiveSheet.Outline.ShowLevels ColumnLevels:=1
End Sub

Open in new window

Thanks
Rob
Thank you all for the help :)
Thank you Experts!