Macro to Clear Contents from Multiple Cells, Excel 2013

ACF0303

New Member
Joined
Aug 29, 2013
Messages
41
I am trying to write a macro to clear the code from a large number of cells. I've tried to get a head start by using the record function as I am not VBA pro. I know there is a lot of superfluous stuff in here and I would like make it as clean as possible. I am also getting an error and I think it is due to the length of the range, but I have no idea. Any help would be very welcome. Here is the whole mess (I apologize for the lack of structure):

CODE

Sub Clear_Text_Talent_Acquisition()
'
' Clear_Text_Talent_Acquisition Macro
'

'
Range( _
"T6:AE6,T7:AE7,T9:AE9,T14:AE14,T18:AE18,T22:AE22,T25:AE25,T27:AE27,T31:AE31,T32:AE32,T33:AE33,T36:AE36,T39:AE39,T48:AE48,T53:AE53,T55:AE55,T59:AE59,T60:AE60,T61:AE61,T62:AE62,T69:AE69,T70:AE70,T73:AE73,T75:AE75,T76:AE76,T78:AE78,T89:AE89,T90:AE90,T92:AE92" _
).Select
Union(Range( _
"T99:AE99,T100:AE100,T102:AE102,T6:AE6,T7:AE7,T9:AE9,T14:AE14,T18:AE18,T22:AE22,T25:AE25,T27:AE27,T31:AE31,T32:AE32,T33:AE33,T36:AE36,T39:AE39,T48:AE48,T53:AE53,T55:AE55,T59:AE59,T60:AE60,T61:AE61,T62:AE62,T69:AE69,T70:AE70,T73:AE73,T75:AE75,T76:AE76" _
), Range("T94:AE94,T96:AE96,T98:AE98")).Select
Union(Range( _
"T99:AE99,T100:AE100,T102:AE102,T112:AE112,T129:AE129,T6:AE6,T7:AE7,T9:AE9,T14:AE14,T18:AE18,T22:AE22,T25:AE25,T27:AE27,T31:AE31,T32:AE32,T33:AE33,T36:AE36,T39:AE39,T48:AE48,T53:AE53,T55:AE55,T59:AE59,T60:AE60,T61:AE61,T62:AE62,T69:AE69,T70:AE70,T73:AE73" _
), Range("T90:AE90,T92:AE92,T94:AE94,T96:AE96,T98:AE98")).Select
Union(Range( _
"T99:AE99,T100:AE100,T102:AE102,T112:AE112,T129:AE129,T133:AE133,T134:AE134,T137:AE137,T140:AE140,T6:AE6,T7:AE7,T9:AE9,T14:AE14,T18:AE18,T22:AE22,T25:AE25,T27:AE27,T31:AE31,T32:AE32,T33:AE33,T36:AE36,T39:AE39,T48:AE48,T53:AE53,T55:AE55,T59:AE59,T60:AE60" _
), Range( _
"T75:AE75,T76:AE76,T78:AE78,T89:AE89,T90:AE90,T92:AE92,T94:AE94,T96:AE96,T98:AE98" _
)).Select
Union(Range( _
"T99:AE99,T100:AE100,T102:AE102,T112:AE112,T129:AE129,T133:AE133,T134:AE134,T137:AE137,T140:AE140,T141:AE141,T143:AE143,T147:AE147,T150:AE150,T6:AE6,T7:AE7,T9:AE9,T14:AE14,T18:AE18,T22:AE22,T25:AE25,T27:AE27,T31:AE31,T32:AE32,T33:AE33,T36:AE36,T39:AE39" _
), Range( _
"T62:AE62,T69:AE69,T70:AE70,T73:AE73,T75:AE75,T76:AE76,T78:AE78,T89:AE89,T90:AE90,T92:AE92,T94:AE94,T96:AE96,T98:AE98" _
)).Select
Union(Range( _
"T99:AE99,T100:AE100,T102:AE102,T112:AE112,T129:AE129,T133:AE133,T134:AE134,T137:AE137,T140:AE140,T141:AE141,T143:AE143,T147:AE147,T150:AE150,T157:AE157,T162:AE162,T164:AE164,T6:AE6,T7:AE7,T9:AE9,T14:AE14,T18:AE18,T22:AE22,T25:AE25,T27:AE27,T31:AE31,T32:AE32" _
), Range( _
"T59:AE59,T60:AE60,T61:AE61,T62:AE62,T69:AE69,T70:AE70,T73:AE73,T75:AE75,T76:AE76,T78:AE78,T89:AE89,T90:AE90,T92:AE92,T94:AE94,T96:AE96,T98:AE98" _
)).Select
Union(Range( _
"T99:AE99,T100:AE100,T102:AE102,T112:AE112,T129:AE129,T133:AE133,T134:AE134,T137:AE137,T140:AE140,T141:AE141,T143:AE143,T147:AE147,T150:AE150,T157:AE157,T162:AE162,T164:AE164,T167:AE167,T168:AE168,T174:AE174,T177:AE177,T6:AE6,T7:AE7,T9:AE9,T14:AE14,T18:AE18" _
), Range( _
"T39:AE39,T48:AE48,T53:AE53,T55:AE55,T59:AE59,T60:AE60,T61:AE61,T62:AE62,T69:AE69,T70:AE70,T73:AE73,T75:AE75,T76:AE76,T78:AE78,T89:AE89,T90:AE90,T92:AE92,T94:AE94,T96:AE96,T98:AE98" _
)).Select
Union(Range( _
"T99:AE99,T100:AE100,T102:AE102,T112:AE112,T129:AE129,T133:AE133,T134:AE134,T137:AE137,T140:AE140,T141:AE141,T143:AE143,T147:AE147,T150:AE150,T157:AE157,T162:AE162,T164:AE164,T167:AE167,T168:AE168,T174:AE174,T177:AE177,T184:AE184,T6:AE6,T7:AE7,T9:AE9" _
), Range( _
"T36:AE36,T39:AE39,T48:AE48,T53:AE53,T55:AE55,T59:AE59,T60:AE60,T61:AE61,T62:AE62,T69:AE69,T70:AE70,T73:AE73,T75:AE75,T76:AE76,T78:AE78,T89:AE89,T90:AE90,T92:AE92,T94:AE94,T96:AE96,T98:AE98" _
)).Select
Union(Range( _
"T99:AE99,T100:AE100,T102:AE102,T112:AE112,T129:AE129,T133:AE133,T134:AE134,T137:AE137,T140:AE140,T141:AE141,T143:AE143,T147:AE147,T150:AE150,T157:AE157,T162:AE162,T164:AE164,T167:AE167,T168:AE168,T174:AE174,T177:AE177,T184:AE184,T203:AE203,T206:AE206" _
), Range( _
"T31:AE31,T32:AE32,T33:AE33,T36:AE36,T39:AE39,T48:AE48,T53:AE53,T55:AE55,T59:AE59,T60:AE60,T61:AE61,T62:AE62,T69:AE69,T70:AE70,T73:AE73,T75:AE75,T76:AE76,T78:AE78,T89:AE89,T90:AE90,T92:AE92,T94:AE94,T96:AE96,T98:AE98" _
)).Select
Union(Range( _
"T99:AE99,T100:AE100,T102:AE102,T112:AE112,T129:AE129,T133:AE133,T134:AE134,T137:AE137,T140:AE140,T141:AE141,T143:AE143,T147:AE147,T150:AE150,T157:AE157,T162:AE162,T164:AE164,T167:AE167,T168:AE168,T174:AE174,T177:AE177,T184:AE184,T203:AE203,T206:AE206" _
), Range( _
"T27:AE27,T31:AE31,T32:AE32,T33:AE33,T36:AE36,T39:AE39,T48:AE48,T53:AE53,T55:AE55,T59:AE59,T60:AE60,T61:AE61,T62:AE62,T69:AE69,T70:AE70,T73:AE73,T75:AE75,T76:AE76,T78:AE78,T89:AE89,T90:AE90,T92:AE92,T94:AE94,T96:AE96,T98:AE98" _
)).Select
Selection.ClearContents
Range("T6:AE6").Select
End Sub

Thanks,
Cathy :confused:
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Without going blind trying to read ALL the code...:LOL:
It looks like you are simply trying to clear the range
Code:
Range("T6:AE102").ClearContents
 

ACF0303

New Member
Joined
Aug 29, 2013
Messages
41
Thanks Michael, I may be partially blind myself at this point. I have a combination of things happening in the spreadsheet. The macro above was to clear the text out of specific cells. Would the suggestion above remove text from locked cells? (I don't want all cells cleared, just some)

Thanks again,
Cathy
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
If the sheet is unprotected, yes it will clear all.
otherwise it will only do the Unlocked cells.
So if you have T6:AE102 as a complete range....how many cells in that range need to stay uncleared ?
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Maybe something like:

Code:
Sub test()
Dim vRow, vClearRows
vClearRows = Array("6:7", 9, "33:34") 'list all your rows
For Each vRow In vClearRows
    Application.Intersect(Columns("T:AE"), Rows(vRow)).ClearContents
Next
End Sub
 

ACF0303

New Member
Joined
Aug 29, 2013
Messages
41
Many of the alternating rows have a series of checkboxes (the workbook is data collection tool so it has questions that are open-ended - text response and those that have a limited set of answers - checkboxes). I have to leave the cells with checkboxes unlocked so users can select them, but I don't want them to disappear if I clear the contents.
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you are using Form Check Boxes or ActiveX Check Boxes then you won't delete them by clearing the cell that they reside over, or even the contents of a linked cell if you have one. You should also be able to select / toggle these controls whether the sheet is protected or not (though you will have to leave any linked cells unlocked).
 

ACF0303

New Member
Joined
Aug 29, 2013
Messages
41
Still struggling. The cells I want to clear are non-contiguous so I cannot name one whole range (T3:AE203) and be done. Because I have so many cells to clear, I am running into an error with the Range(). Maybe too many arguments? Is there a character limit? I think I must use Range and Union Range, but could use some guidance on how to make them cooperate.

Thanks so much,
Cathy
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
The code from Teeroy should do the trick.
But if not, is there something in the cells that need clearing that is common to all ?
I don't think you really need Union(range(....Have you tried reducing the ranges into smaller lines, even though I don't believe this would be an issue
Also, if the ranges are constant, have you considered highlighting all the cells to be cleared and then creating a named range.
Then you could simply clear that named range !
 

Watch MrExcel Video

Forum statistics

Threads
1,123,083
Messages
5,599,643
Members
414,326
Latest member
kfg1287

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
Top