How do I speed up VBA code that clears cells

[Bobbbo]

I have a worksheet that has a number of cells that need to be cleared. When I use the F8 key on the keyboard to walk through the VBA code I noticed that it takes seconds to "Clear Contents" The cells I need to clear are not contiguious. Is there a more efficent and faster way to clear cells? Speed is very important in this code. The code is below.

Sheets("DATA ENTRY").Select
        Range("AA12:DV48").Select
        Selection.ClearContents
        Range("F12:Z12").Select 
        Selection.ClearContents
        Range("F15:Z15").Select 
        Selection.ClearContents
        Range("F19:Z20").Select 
        Selection.ClearContents
        Range("F28:Z28").Select 
        Selection.ClearContents
        Range("G41:Z42").Select 
        Selection.ClearContents
        Range("G44:Z45").Select 
        Selection.ClearContents
        Range("G47:Z48").Select 
        Selection.ClearContents
        Range("G51:Z51").Select  
        Selection.ClearContents
        Range("G56:Z56").Select  
        Selection.ClearContents
        Range("G58:Z61").Select   
        Selection.ClearContents
        Range("G64:Z68").Select 
        Selection.ClearContents
        Range("F79:Z85").Select  
        Selection.ClearContents
        Range("F89:Z92").Select  
        Selection.ClearContents
        Range("F94:Z94").Select  
        Selection.ClearContents
        Range("F96:Z96").Select  
        Selection.ClearContents
        Range("G101:Z102").Select  
        Selection.ClearContents
        Range("G104:Z109").Select  
        Selection.ClearContents
        Range("G111:Z111").Select  
        Selection.ClearContents
        Range("F138:Z138").Select 
        Selection.ClearContents

Thanks in advance.

[Richard Buttrey]

Hi

The most important thing you should do is remove all the 'Selection' stuff

i.e. instead of

Range("AA12:DV48").Select
Selection.ClearContents

just use

Range("AA12:DV48").ClearContents

It's rarely necessary to select or activate stuff. Just address any objects/ranges etc. directly.

It would be easier to comment further if we had the workbook but if you're able to use Autofilter to filter rows 12, 15, 19:20, ...etc then you could then use a

Range("F12:Z138").SPecialCells(xlCellTypeVisible).ClearContents

[cytop]

First off - don't use Select... just clear the ranges directly

Range("G56:Z56").ClearContents

... just to start with.

[Arkadi]

Doing the whole clear at once might be more efficient also, but definitely all those .select are redundant. Try this:

Dim myRange As Range
With Sheets("DATA ENTRY")
    Set myRange = Union(.Range("AA12:DV48"), .Range("F12:Z12"), .Range("F15:Z15"), .Range("F19:Z20"), .Range("F28:Z28"), _
    .Range("G41:Z42"), .Range("G44:Z45"), .Range("G47:Z48"), .Range("G51:Z51"), .Range("G56:Z56"), .Range("G58:Z61"), _
    .Range("G64:Z68"), .Range("F79:Z85"), .Range("F89:Z92"), .Range("F94:Z94"), .Range("F96:Z96"), .Range("G101:Z102"), _
    .Range("G104:Z109"), .Range("G111:Z111"), .Range("F138:Z138"))
End With
myRange.ClearContents

[kvsrinivasamurthy]

One example
Change this code

Sheets("DATA ENTRY").select
        Range("AA12:DV48").Select
        Selection.ClearContents
        Range("F12:Z12").Select
        Selection.ClearContents
        Range("F15:Z15").Select
        Selection.ClearContents

As

Sheets("DATA ENTRY").Range("AA12:DV48, F12:Z12, F15:Z15").ClearContents

[Bobbbo]

One example
Change this code

Sheets("DATA ENTRY").select
        Range("AA12:DV48").Select
        Selection.ClearContents
        Range("F12:Z12").Select
        Selection.ClearContents
        Range("F15:Z15").Select
        Selection.ClearContents

As

Sheets("DATA ENTRY").Range("AA12:DV48, F12:Z12, F15:Z15").ClearContents

Great Idea, thanks so much!

[Arkadi]

If this solves the issue please remember to mark the thread as solved? Thanks.