DIRTY DIRTY DIRTY MACRO - - -Need help Cleaning a "Clear Contents" Macro

J

jamesmev

Board Regular
Joined
Apr 9, 2015
Messages
233
Office Version
  1. 365
Platform
  1. Windows
I am attempting to clear various cells within a workbook. However, writing a macro that clears each cell individually freezes my workbook. I've tried selecting all cells at once then clearing. This is causing a "union" and causing additional issues.
If anyone could help with this that would be great.... This is SUPER DIRTY!
****
Code: 
Sub clearall()
'
' clearall Macro
'


'
Application.ScreenUpdating = False


    
    Range("B222").Activate
    Selection.ClearContents
    Range("B214:P214").Select
    Range("N214").Activate
    Selection.ClearContents
    Range("B211:Q211").Select
    Range("P211").Activate
    Selection.ClearContents
    Range("B208:O208").Select
    Range("K208").Activate
    Selection.ClearContents
 
    Range("B196:R203").Select
    Range("P203").Activate
    Selection.ClearContents
    Range("B193:L193").Select
    Range("I193").Activate
    Selection.ClearContents


    Range("B190:Q190").Select
    Range("O190").Activate
    Selection.ClearContents
    Range("J187").Select
    Selection.ClearContents
   
    Range("B179:Q182").Select
    Range("L182").Activate
    Selection.ClearContents
    Range("B171:R173").Select
    Range("B173").Activate
    Selection.ClearContents


    Range("B168:L168").Select
    Range("J168").Activate
    Selection.ClearContents
    Range("B165:R165").Select
    Range("P165").Activate
    Selection.ClearContents
    Range("B159:F159").Select
    Selection.ClearContents


    Range("B156:O156").Select
    Range("M156").Activate
    Selection.ClearContents
    Range("B153:O153").Select
    Range("N153").Activate
    Selection.ClearContents
    Range("B150:O150").Select
    Range("J150").Activate
    Selection.ClearContents
    Range("E144:G144").Select
    Selection.ClearContents
    Range("B144:C144").Select
    Selection.ClearContents
    Range("E141:G141").Select
    Selection.ClearContents
    Range("B141:C141").Select
    Selection.ClearContents


    Range("B134:S136").Select
    Range("B136").Activate
    Selection.ClearContents
    Range("B129:S131").Select
    Range("B131").Activate
    Selection.ClearContents
    Range("B126:P126").Select
    Range("N126").Activate
    Selection.ClearContents
    Range("B123:Q123").Select
    Range("P123").Activate
    Selection.ClearContents
    Range("B120:O120").Select
    Range("K120").Activate
    Selection.ClearContents


    Range("B99:E115").Select
    Range("B115").Activate
    Selection.ClearContents
    Range("N96").Select
    Selection.ClearContents
    Range("K96:L96").Select
    Selection.ClearContents
    Range("H96:I96").Select
    Selection.ClearContents
    Range("E96:F96").Select
    Selection.ClearContents
    Range("B96:C96").Select
    Selection.ClearContents
    Range("W93:X93").Select
    Selection.ClearContents
    Range("T93:U93").Select
    Selection.ClearContents
    Range("P93:Q93").Select
    Selection.ClearContents
    Range("M93:N93").Select
    Selection.ClearContents
    Range("J93:K93").Select
    Selection.ClearContents
    Range("G93:H93").Select
    Selection.ClearContents
    Range("B93:E93").Select
    Selection.ClearContents
    Range("K90:M90").Select
    Selection.ClearContents
    Range("G90:I90").Select
    Selection.ClearContents
    Range("B90:E90").Select
    Selection.ClearContents


    Range("D74:J85").Select
    Range("H85").Activate
    Selection.ClearContents
 
    Range("K71:L71").Select
    Selection.ClearContents
    Range("H71:I71").Select
    Selection.ClearContents
    Range("E71:F71").Select
    Selection.ClearContents
    Range("B71:C71").Select
    Selection.ClearContents
    Range("W68:X68").Select
    Selection.ClearContents
    Range("S68:U68").Select
    Selection.ClearContents
    Range("O68:Q68").Select
    Selection.ClearContents
    Range("K68:M68").Select
    Selection.ClearContents
    Range("G68:I68").Select
    Selection.ClearContents
    Range("B68:E68").Select
    Selection.ClearContents
    Range("W62:X62").Select
    Selection.ClearContents
    Range("S62:U62").Select
    Selection.ClearContents
    Range("O62:Q62").Select
    Selection.ClearContents
    Range("K62:M62").Select
    Selection.ClearContents
    Range("G62:I62").Select
    Selection.ClearContents
    Range("B62:E62").Select
    Selection.ClearContents


    Range("W59:Y59").Select
    Selection.ClearContents
    Range("S59:U59").Select
    Selection.ClearContents
    Range("O59:Q59").Select
    Selection.ClearContents
    Range("K59:M59").Select
    Selection.ClearContents
    Range("G59:I59").Select
    Selection.ClearContents
    Range("B59:E59").Select
    Selection.ClearContents
    Range("W54:X54").Select
    Selection.ClearContents
    Range("S54:U54").Select
    Selection.ClearContents
    Range("O54:Q54").Select
    Selection.ClearContents
    Range("K54:M54").Select
    Selection.ClearContents
    Range("G54:I54").Select
    Selection.ClearContents
    Range("B54:E54").Select
    Selection.ClearContents
    Range("V50:X51").Select
    Selection.ClearContents
    Range("R50:T50").Select
    Selection.ClearContents
    Range("O50:P50").Select
    Selection.ClearContents
    Range("K50:M50").Select
    Selection.ClearContents
    Range("G50:I50").Select
    Selection.ClearContents
    Range("B50:E50").Select
    Selection.ClearContents
  
    Range("C40:D40").Select
    Selection.ClearContents
  
    Range("S28:T28").Select
    Selection.ClearContents
    Range("O28:Q28").Select
    Selection.ClearContents
    Range("K28:M28").Select
    Selection.ClearContents
    Range("H28:I28").Select
    Selection.ClearContents
    Range("C28:F28").Select
    Selection.ClearContents
    Range("H25:M25").Select
    Selection.ClearContents
    Range("C25:F25").Select
    Selection.ClearContents
    Range("O22:T22").Select
    Selection.ClearContents
    Range("H22:M22").Select
    Selection.ClearContents
    Range("C22:F22").Select
    Selection.ClearContents
       Range("Q14:R14").Select
    Selection.ClearContents
    Range("K14").Select
    Selection.ClearContents
    Range("E14:G14").Select
    Selection.ClearContents
    Range("Q11:T11").Select
    Selection.ClearContents
    Range("J11:M11").Select
    Selection.ClearContents
    Range("D11:F11").Select
    Selection.ClearContents
    Range("Q7:T7").Select
    Selection.ClearContents
    Range("J7:M7").Select
    Selection.ClearContents
    Range("D7:F7").Select
    Selection.ClearContents
    Application.ScreenUpdating = True
    
End Sub
 
Last edited by a moderator:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Hi there, I'm not exactly sure which cells you are trying to clear based on this code. Is there some criteria to clear the cells or are the cells that will be cleared just selected at random or do you wish to clear the contents of every cell on the sheet?
 
Upvote 0
The first section of code can be slimmed down to
Code: 
Range("B222").ClearContents
Range("B214:P214").ClearContents
Range("B211:Q211").ClearContents
Range("B208:O208").ClearContents
If you do the same thing for the rest of your macro, somebody will probably show you how to simplify it even further
 
Upvote 0
Another possible way of looking at this is to lookat what you want remaining on your worksheet: Is what remains just data? if this is the case then a much faster way would be to load the entire sheet into a variant array, delete the contents from the entire sheets., then go through the array deleting the contents from the elements you want to delete, then writing the array back to the worksheet.
 
Upvote 0
jamesmev said:
I've tried selecting all cells at once then clearing. This is causing a "union" and causing additional issues.
Click to expand...
If you are still having that problem you might need to clarify just what you mean by it but this worked on my sample worksheet.
Code: 
Sub Clear_All()
  Union(Range("D7:F7,J7:M7,Q7:T7,D11:F11,J11:M11,Q11:T11,E14:G14,K14,Q14:R14,C22:F22,H22:M22,O22:T22,C25:F25,H25:M25,C28:F28,H28:I28,K28:M28,O28:Q28,S28:T28,C40:D40"), _
        Range("B50:E50,G50:I50,K50:M50,O50:P50,R50:T50,V50:X51,B54:E54,G54:I54,K54:M54,O54:Q54,S54:U54,W54:X54,B59:E59,G59:I59,K59:M59,O59:Q59,S59:U59,W59:Y59,B62:E62"), _
        Range("G62:I62,K62:M62,O62:Q62,S62:U62,W62:X62,B68:E68,G68:I68,K68:M68,O68:Q68,S68:U68,W68:X68,B71:C71,E71:F71,H71:I71,K71:L71,D74:J85,B90:E90,G90:I90,K90:M90"), _
        Range("B93:E93,G93:H93,J93:K93,M93:N93,P93:Q93,T93:U93,W93:X93,B96:C96,E96:F96,H96:I96,K96:L96,N96,B99:E115,B120:O120,B123:Q123,B126:P126,B129:S131,B134:S136"), _
        Range("B141:C141,E141:G141,B144:C144,E144:G144,B150:O150,B153:O153,B156:O156,B159:F159,B165:R165,B168:L168"), _
        Range("B171:R173,B179:Q182,J187,B190:Q190,B193:L193,B196:R203,B208:O208,B211:Q211,B214:P214,B222")).ClearContents
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Need Help Clearing Specific Data in multiple columns and rows Using a Macro
Replies
3
Views
411
apo
A
J
  • Question Question
Macro to clear content of cells
Replies
4
Views
772
jammoca
J
K
  • Question Question
Clearing cell contents with macro
Replies
3
Views
580
kool_mas
K

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top