Memory issues with macros

[paul_taylor]

I ran a macro to loop through all the sheets (about 10) in a workbook and clear out a certain range. The file grew from 200K to 5 MB. What's going on here? Can't figure it out.

 

[Mike Blackman]

Can we see the macro in question?

 

[paul_taylor]

Sub Macro3()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim sheetloop As Long


sheetloop = 4
Do While sheetloop <= 14

Select Case sheetloop
Case 4, 5, 6, 8, 9, 10, 11, 13, 14
Sheets(sheetloop).Activate
Range("A5:z10000").Select
Selection.Clear


Case 7
Sheets(sheetloop).Activate
Range("A6:z10000").Select
Selection.Clear

Case 12

Sheets(sheetloop).Activate
Range("A11:z10000").Select
Selection.Clear


End Select

sheetloop = sheetloop + 1
Loop






Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

 

[Mike Blackman]

Ho many rows of data were there previously in each tab?

 

[paul_taylor]

Ho many rows of data were there previously in each tab?

Only maybe 20 for now, but will grow between 2 and 10,000 depending on another macro that will be run. By clearing to 10,000 do I add memory?

 

[Mike Blackman]

Ok, by clearing the values between row 20 & 10000 you have essentially made them active and this automatically increases file size, had you used delete (which is slightly less efficient that clear) you probably wouldn't have had this problem.

There is also no need to activate the sheets to do this or to select the cells.

Try this though, I have assumed Column A has contiguous data and have used this to define last row, if not change to suit.

Sub Macro3()
    
    Dim sheetloop As Long
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    sheetloop = 4
    For sheetloop = 4 To 14
        Select Case sheetloop
            Case 4, 5, 6, 8, 9, 10, 11, 13, 14
                With Sheets(sheetloop)
                    .Range("A5:Z" & .Range("A" & Rows.Count).End(xlUp).Row).Clear
                End With
            Case 7
                With Sheets(sheetloop)
                    .Range("A6:Z" & .Range("A" & Rows.Count).End(xlUp).Row).Clear
                End With
            Case 12
                With Sheets(sheetloop)
                    .Range("A11:Z" & .Range("A" & Rows.Count).End(xlUp).Row).Clear
                End With
        End Select
    Loop
    
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    
End Sub

 

[paul_taylor]

Thanks for the tip!

 

[Mike Blackman]

Np, did you try the macro?