AutoFit Columns in specific worksheet

[Dundas]

Hi,

can anyone help me alter this macro so that it works in specific worksheet, not for the whole work book.

Please Login or Register  to view this content.

The macro works fine, but i would like it to not affect all the active sheets.

many thanks!

[Richard Buttrey]

Why do you need to loop through all the cells in the range?

Just use the following in the Worksheet's Change event not the Workbook Change

Please Login or Register  to view this content.

[Dundas]

Thanks...

tried...comes up with the following error..

Compile error:Ambiguous name detected: Worksheet_Change

i want any the columns to autofit as data is entered into the cells in that column, but there are hidden columns that i want to remain hidden
the original code works fine, but i have a summary sheet at the start which i dont want to alter

hope this is clearer...

[Richard Buttrey]

Did you put the line of code
Target.EntireColumn.AutoFit

inside the Sheet Change event procedure that you see in the VBE when you select the sheet. I suspect you've just added the code I gave you as a module level procedure, or perhaps added it manually in the sheet code area.

Given your addendum, rather than just use Target.EntireColumn......then just use
Cells.EntireColumn.Autofit

and then some code to hide the columns you don't want to see. e.g.

Range("E:E").ColumnWidth = 0

[Dundas]

Thanks Richard,
i have managed to merge your solution with my original one and have found a suitable, if not entirely perfect outcome.

Please Login or Register  to view this content.

Thanks again for your help....

[Cutter]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save