Macro to Clear Contents from Multiple Cells, Excel 2013

[ACF0303]

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

 

[Michael M]

Without going blind trying to read ALL the code...
It looks like you are simply trying to clear the range

Range("T6:AE102").ClearContents

 

[ACF0303]

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]

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]

Maybe something like:

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]

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]

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]

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]

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 !