Autofit column Help?

Tim

unread,

Jan 6, 2010, 6:17:02 PM1/6/10

to

Hi there,
The code below works great ... BUT ... I need it to apply to only columns G
and H ...PLUS ... I would like to make this across the 25 worksheets I have.
Any ideas?
Thanks very much.
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Application.ActiveWorkbook.RefreshAll
Target.EntireColumn.AutoFit

On Error Resume Next
Target.Dependents.EntireColumn.AutoFit
End Sub

OssieMac

unread,

Jan 6, 2010, 7:32:01 PM1/6/10

to

Hi Tim,

Try the following

Private Sub Worksheet_Change(ByVal Target As Range)

Dim isect As Range
Dim ws As Worksheet

Set isect = Application.Intersect _
(Target, Columns("G:H"))
If Not isect Is Nothing Then
Me.Application.ActiveWorkbook.RefreshAll
For Each ws In Worksheets
ws.Columns("G:H").AutoFit
Next ws

'Not sure if you still want the following

On Error Resume Next
Target.Dependents.EntireColumn.AutoFit

End If

End Sub

If you want to exclude any sheets then use the following code in the loop.
You can expand the if statement if more than one sheet to excleude.

For Each ws In Worksheets
If ws.Name <> "MyShtToExclude" Then
ws.Columns("G:H").AutoFit
End If
Next ws

--
Regards,

OssieMac

unread,

Jan 6, 2010, 7:41:01 PM1/6/10

to

Hi again Tim,

Not sure now whether you meant that you wanted all worksheets to have the
auto column fit if the active one was changed or if you only want the active
sheet changed but whatever worksheet is the activesheet.

The following just changes the activesheet (whatever one that might be) but
you need to copy it into ThisWorkbook module.

Private Sub Workbook_SheetChange _
(ByVal Sh As Object, ByVal Target As Range)

Dim isect As Range

Set isect = Application.Intersect _
(Target, Sh.Columns("G:H"))

If Not isect Is Nothing Then

ActiveWorkbook.RefreshAll
Sh.Columns("G:H").AutoFit

'Not sure if you still want the following

On Error Resume Next
Target.Dependents.EntireColumn.AutoFit

End If

End Sub

--
Regards,

OssieMac

Tim

unread,

Jan 7, 2010, 10:26:01 PM1/7/10

to

Hi there,
This code works great....I tried your next post, but it kinda of went for a
loop that I could not break. I do want this code to apply to all other
worksheets, but only when they are active. I will copy this code to all
sheets unless, you can think of another way. Thanks for your help.

Tim

unread,

Jan 7, 2010, 10:31:01 PM1/7/10

to

I may have spoke too soon...I copied the code to other worksheets and it does
not work. Not sure what I am doing wrong? Any ideas?

OssieMac

unread,

Jan 8, 2010, 2:16:01 AM1/8/10

to

Hi Tim,

If you use the code from my second post then you must copy it into
ThisWorkbook module; not the Sheet module.

Also you must delete my other code from the Sheet modules.

In case you don't understand what I mean then when you are in the VBA
Editor, on the left hand side there is a column (Window) that is called the
Project Explorer with a list of the modules. (If you can't see this Window
then Select menu item View -> Project Explorer.) In this window you will see
a list of the Sheet modules and also one called ThisWorkbook. Double Click
ThisWorkbook and copy the code from my second post into it.

Double click each of the other sheet modules in turn and delete my other
code from them. (You cannot have any subs called Private Sub
Worksheet_Change(ByVal Target As Range) in the individual sheet modules.)

I have tested the code in my second post and it works on whatever is the
active sheet.

Hope this helps.

--
Regards,

OssieMac