Saving File is Slow

**ccpsc** · 03-19-2013, 03:52 PM

Open the attached file and click save it will take the normal amount of time to do this function.

Click on the CLEAR ALL button at the top of column J then click save now it takes a lot longer to do this function.

Can someone tell me how to fix my code to fix this?

**PcSaggio** · 03-19-2013, 05:18 PM

Hi,

I tested the file with Excel 2013.
The file behaves correctly, the sub is adding formatting to the sheet.
Try to delete the lines to speed up the sheet.

**ccpsc** · 03-19-2013, 05:24 PM

Thanks for the reply. Is the line ".Interior.Pattern = xlNone" causing the problem?

I am trying to remove Fill from cells in the sheets. Is there a better way to do this?

I am a novice at coding so I don't assume I know anything

**Leith Ross** · 03-19-2013, 06:01 PM

Hello ccpsc,

This version of the macro works. and is faster. In VBA, it is rarely necessary to select an object before performing an action on it. Selecting objects slows the macro down. You also were using a set range which was quite large for each sheet. Excel keeps track of the cells used on worksheet. This can be returned as a single range using the UsedRange property of the worksheet. Since every worksheet has headers in row 1, only the cells below this row need to be cleared.

Sub ClearAll()

    Dim Rng As Range
    Dim Wks As Worksheet
    
        For Each Wks In Worksheets
            Set Rng = Wks.UsedRange.Offset(1, 0)
            With Rng
                .ClearContents
                .Interior.Pattern = xlPatternNone
                .Interior.ColorIndex = xlColorIndexNone
            End With
        Next Wks
        
End Sub