Specify Minimum Row Height And Autofit Row Size for ALL worksheets

[Laurence D]

Hello experts,

I have a workbook with the same kind of data in about 30 different worksheet tabs. I want to make all of the rows with data in each worksheet be reformatted with a row height of 30, unless the content is larger than 30 then it needs the entire row to be autofitted. I have code that works for one active sheet.

But want I need help with is additional code that will make my code work with all worksheets in this workbook.

Can anybody help with this? Here is my code so far:

[COLOR=#000000][FONT=Menlo]Sub MinimumRow_Plus_Autofit()[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#000000][FONT=Menlo] Application.ScreenUpdating = [COLOR=#011993]False[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]For[/COLOR] [COLOR=#011993]Each[/COLOR] c [COLOR=#011993]In[/COLOR] Range("A1:A300")[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    c.EntireRow.AutoFit[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    [COLOR=#011993]If[/COLOR] c.RowHeight < 30 [COLOR=#011993]Then[/COLOR] c.RowHeight = 30[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]Next[COLOR=#000000] c[/COLOR][/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#000000][FONT=Menlo]Application.ScreenUpdating = [COLOR=#011993]True[/COLOR][/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]End[COLOR=#000000] [/COLOR]Sub[/FONT][/COLOR]

Thanks all,

 

[gallen]

Sub MinimumRow_Plus_Autofit()

Dim ws As Worksheet
Dim c As Range

    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        For Each c In ws.Range("A1:A300")
            c.EntireRow.AutoFit
            If c.RowHeight < 30 Then c.RowHeight = 30
        Next c
    Next ws
    
    Application.ScreenUpdating = True
EndSub

 

[Laurence D]

Perfect thanks Gallen,

Legend.

 

[gallen]

Very welcome

 

[Laurence D]

Hello again,

I have been trying this code out with large numbers of worksheets (50 to 100 worksheets) and it is functional but is really slow at performing the assigned task (e.g. taking more than 5 minutes each run!!). Is there a way to make this code more efficient as I assume the way that the macro is written is chunky and slowing the process?

For it to work perfectly for me I need the macro to find all non-blank rows in columns A:G (each worksheet is no more than 300 rows down) and then perform the above coding.

Appreciate any help,

Thanks

 

[gallen]

I can only assume there's a lot of calculating going on in the background. Try this:

Sub MinimumRow_Plus_Autofit()

Dim ws As Worksheet
Dim c As Range


    Application.ScreenUpdating = False
    [COLOR=#ff0000][B]Application.Calculation = xlCalculationManual[/B][/COLOR]
    For Each ws In ThisWorkbook.Worksheets
        For Each c In ws.Range("A1:A300")
            c.EntireRow.AutoFit
            If c.RowHeight < 30 Then c.RowHeight = 30
        Next c
    Next ws
    [B][COLOR=#ff0000]Application.Calculation = xlCalculationAutomatic[/COLOR][/B]
    Application.ScreenUpdating = True
EndSub