Don't all laugh at once

[jamesmev]

Clearly I need help with VBA. I recorded a macro to clear various cells. However this slows the entire sheet down.
is there a way to achieve the below an cleaner way that won't make my screen blink continually?

Application.ScreenUpdating = True


Range("B138:Q149").Select
Range("O149").Activate
Selection.ClearContents
Range("K133:Q135").Select
Selection.ClearContents
Range("F134:H134").Select
Selection.ClearContents
Range("B134:D134").Select
Selection.ClearContents


Range("K126:Q127").Select
Selection.ClearContents
Range("G127:H127").Select
Selection.ClearContents
Range("B127:E127").Select
Selection.ClearContents
Range("J124:M124").Select
Selection.ClearContents
Range("G124:H124").Select
Selection.ClearContents
Range("B124:E124").Select
Selection.ClearContents

Range("B108:T118").Select
Range("R118").Activate
Selection.ClearContents


Range("B97:S102").Select
Range("B102").Activate
Selection.ClearContents


Range("B94:P94").Select
Range("N94").Activate
Selection.ClearContents
Range("B91:Q91").Select
Range("P91").Activate
Selection.ClearContents
Range("B88:O88").Select
Range("K88").Activate
Selection.ClearContents

Range("B76:R83").Select
Range("P83").Activate
Selection.ClearContents
Range("B73:L73").Select
Range("I73").Activate
Selection.ClearContents
Range("B70:Q70").Select
Range("O70").Activate
Selection.ClearContents

Range("L62:R64").Select
Selection.ClearContents
Range("B63:I63").Select
Range("F63").Activate
Selection.ClearContents
Range("B59:O61").Select
Range("J61").Activate
Selection.ClearContents

Range("B51:R53").Select
Range("B53").Activate
Selection.ClearContents
Range("B48:L48").Select
Range("J48").Activate
Selection.ClearContents

Range("B45:R45").Select
Range("P45").Activate
Selection.ClearContents
Range("B39:F39").Select
Selection.ClearContents
Range("B36:O36").Select
Range("M36").Activate
Selection.ClearContents

Range("B33:O33").Select
Range("N33").Activate
Selection.ClearContents
Range("B30:O30").Select
Range("J30").Activate
Selection.ClearContents
Range("E24:G24").Select
Selection.ClearContents
Range("B24:C24").Select
Selection.ClearContents

Range("E21:G21").Select
Selection.ClearContents
Range("B21:C21").Select
Selection.ClearContents
Range("R15:U15").Select
Selection.ClearContents
Range("N15:P15").Select
Selection.ClearContents
Range("K15:L15").Select
Selection.ClearContents
Range("F15:I15").Select
Selection.ClearContents
Range("B15:D15").Select
Selection.ClearContents
Range("R12:U12").Select
Selection.ClearContents
Range("O12:P12").Select
Selection.ClearContents
Range("K12:M12").Select
Selection.ClearContents
Range("F12:I12").Select
Selection.ClearContents
Range("B12:D12").Select
Selection.ClearContents
Range("E6:G6").Select
Selection.ClearContents
Range("E4:G4").Select
Selection.ClearContents
Application.ScreenUpdating = False
End Sub

 

[paulxmw]

why do you want to delete these sells

 

[Eric W]

The most obvious thing is you have the Application.ScreenUpdating lines reversed - the False should be the first line, True should be the last line.

Next, although the recorder is a great tool, you can often clean up the code significantly. For example,

Range("K126:Q127").Select
Selection.ClearContents

can be combined to

Range("K126:Q127").ClearContents

and it runs faster.

 

[Joe4]

Range("K126:Q127").Select
Selection.ClearContents

can be combined to

Range("K126:Q127").ClearContents

and it runs faster.

Just to add to Eric's point, most of the time it is not necessary to Select the ranges you want to work on. And actually, Selecting them slows things down.
So the more Select you can get rid of (using the method Eric showed), the better your code will perform. And as you see, you have a LOT of them you can clean up!

 

[MARK858]

And you can do the ranges all at once something like ...

Range("B45:R45,P45,B39:F39,B36:O36,M36").ClearContents

Hopefully I have made no typos on my phone and we don't laugh as we have all been there.

 

[Rick Rothstein]

Using the information provided by others above, all of that code you posted earlier can be boiled down to two lines of quickly executing code (if it were not for the 255 character limit for the length of the text argument to the Range object, it could have been a single line of code)...

[table="width: 500"]
[tr]
	[td]Sub ClearContentsOfManyRanges()
  Range("B97:S102,B108:T118,B124:E124,G124:H124,J124:M124,K126:Q127,B127:E127,G127:H127,K133:Q135,B134:D134,F134:H134,B138:Q149,E4:G4,E6:G6,B12:D12,F12:I12,K12:M12,O12:P12,R12:U12,B15:D15,F15:I15").ClearContents
  Range("K15:L15,N15:P15,R15:U15,B21:C21,E21:G21,B24:C24,E24:G24,B30:O30,B33:O33,B36:O36,B39:F39,B45:R45,B48:L48,B51:R53,B59:O61,L62:R64,B63:I63,B70:Q70,B73:L73,B76:R83,B88:O88,B91:Q91,B94:P94").ClearContents
End Sub[/td]
[/tr]
[/table]

 

[jamesmev]

Using the information provided by others above, all of that code you posted earlier can be boiled down to two lines of quickly executing code (if it were not for the 255 character limit for the length of the text argument to the Range object, it could have been a single line of code)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ClearContentsOfManyRanges()
  Range("B97:S102,B108:T118,B124:E124,G124:H124,J124:M124,K126:Q127,B127:E127,G127:H127,K133:Q135,B134:D134,F134:H134,B138:Q149,E4:G4,E6:G6,B12:D12,F12:I12,K12:M12,O12:P12,R12:U12,B15:D15,F15:I15").ClearContents
  Range("K15:L15,N15:P15,R15:U15,B21:C21,E21:G21,B24:C24,E24:G24,B30:O30,B33:O33,B36:O36,B39:F39,B45:R45,B48:L48,B51:R53,B59:O61,L62:R64,B63:I63,B70:Q70,B73:L73,B76:R83,B88:O88,B91:Q91,B94:P94").ClearContents
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thank
You all for your help. This worked out perfectly.