locked
Column Auto Fit on several sheets RRS feed

  • Question

  • Dear all,

    I have different sheets (formated as table) in my excel file (one main & others "slaves", just returning some info of the first sheet).

    I would like that all columns of all sheets to automatically adjust the width of all columns depending on what is typed in the main sheet.

    I have tried with the below event macro in the worksheet code area of each sheet, but it doesn't adjust the width of the columns of the "slaves" sheets, unless / until you edit the content / formula / result of the cells in the "slaves" sheets.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Target.EntireColumn.AutoFit
    End Sub

    What should be added in the above macro to say : when editing / entering data in main sheet, columns of all sheets (main & slaves) must be width auto adjusted.

    With example :

    Sheet 1 (main), data in column A, B, C, D, etc...

    Sheet 2 (slave), data from column A, B, E, G, H, Z from sheet 1

    Sheet 3 (slave), data from column A, C, D, F, R, S, T from sheet 1

    Or is there an other way of doing such files / sheets with pivot table...

    Note that at the end I want to hide the headings (A,B,C,... & 1,2,3,...)

    Thank you.

    Wednesday, May 10, 2017 6:19 AM

Answers

  • Hello,

    >>it doesn't adjust the width of the columns of the "slaves" sheets, unless / until you edit the content / formula / result of the cells in the "slaves" sheets.

    Worksheet.Change Event occurs when cells on the worksheet are changed by the user or by an external link. So only when you edit the content, the event would fire and the columns would auto fit.

    You could use Worksheet.Calculate Event (Excel) of the "slaves" sheets. The event would occur after the worksheet is recalculated.

    Since there are formulas in the "slaves" sheets which reference the main sheet, if you are in Automatic Calculation mode, when you change the value in the main sheet, all the formula in the slaves sheets would be recalculated, then the worksheet.calculate event would fire, so the auto fit would be appiled.

    If your calculation mode is Manual, you need to handle the Worksheet.change event of the main sheet. Hard code the range of slaves" sheets and autofit their columns.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by sevphil Thursday, May 18, 2017 7:45 AM
    Friday, May 12, 2017 6:10 AM

All replies

  • Hi,

    I notice that your are looking for the Macro codes to meet your requirement. This forum focuses on the general discussion for Excel.

    To better resolve your issue, I would move the thread to Excel for developers forum for more suggestion:

    https://social.msdn.microsoft.com/forums/en-us/home?forum=exceldev

    Hope you can find the solution there :)


    Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, May 11, 2017 2:04 AM
  • Hello,

    >>it doesn't adjust the width of the columns of the "slaves" sheets, unless / until you edit the content / formula / result of the cells in the "slaves" sheets.

    Worksheet.Change Event occurs when cells on the worksheet are changed by the user or by an external link. So only when you edit the content, the event would fire and the columns would auto fit.

    You could use Worksheet.Calculate Event (Excel) of the "slaves" sheets. The event would occur after the worksheet is recalculated.

    Since there are formulas in the "slaves" sheets which reference the main sheet, if you are in Automatic Calculation mode, when you change the value in the main sheet, all the formula in the slaves sheets would be recalculated, then the worksheet.calculate event would fire, so the auto fit would be appiled.

    If your calculation mode is Manual, you need to handle the Worksheet.change event of the main sheet. Hard code the range of slaves" sheets and autofit their columns.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by sevphil Thursday, May 18, 2017 7:45 AM
    Friday, May 12, 2017 6:10 AM