VBA clear contents of multiple ranges.

[willow1985]

I was hoping someone could help me with the below code. I need to clear multiple ranges as shown below but get a runtime error on the large Range: 1004 Method 'Range' of object' Global failed.

Any help fixing this would be greatly appreciated.

Thank you

Sub Clear1()
'
' Clear1 Macro
 
 Range("A2,A4, A6:B10, A14:B14, A16:B16, A20:B20, A22:B22, A24:B24, A27:B27, A29:B29, A31:B31, A34:B34, A36:B36, A38:B38, A41:B41, A43:B43, A45:B45, A48:B48, A50:B50, A52:B52, A55:B55, A57:B57, A59:B59, A62:B62, A64:B64, A66:B66, A69:B69, A71:B71, A73:B73, A76:B76, A78:B78, A80:B80, A83:B83, A85:B85, A87:B87, A90:B90, A92:B92, A94:B94, A97:B97, A99:B99, A101:B101").ClearContents

    Dim o As Object
    For Each o In ActiveSheet.OLEObjects
        If InStr(1, o.Name, "CheckBox") > 0 Then
            o.Object.Value = False
        End If
    Next

    Range("A1").Select
End Sub

 

[DanteAmor]

Apparently it doesn't support that many ranges. I separated it into 2 lines:

Sub Clear1()
'
' Clear1 Macro
 
  Range("A2, A4, A6:B10,  A14:B14, A16:B16, A20:B20," & _
        "A22:B22, A24:B24, A27:B27, A29:B29, A31:B31," & _
        "A34:B34, A36:B36, A38:B38, A41:B41, A43:B43," & _
        "A45:B45, A48:B48, A50:B50, A52:B52, A55:B55").ClearContents
  Range("A57:B57, A59:B59, A62:B62, A64:B64, A66:B66," & _
        "A69:B69, A71:B71, A73:B73, A76:B76, A78:B78," & _
        "A80:B80, A83:B83, A85:B85, A87:B87, A90:B90," & _
        "A92:B92, A94:B94, A97:B97, A99:B99, A101:B101").ClearContents
  
  Dim o As Object
  For Each o In ActiveSheet.OLEObjects
     If InStr(1, o.Name, "CheckBox") > 0 Then
         o.Object.Value = False
     End If
  Next
  
  Range("A1").Select
End Sub

 

[Micron]

I've read that Range() can only accept 255 characters.
EDIT - too late!

 

[dreid1011]

It looks like it is a limit on how many individual ranges you can put into RANGE(). This worked below:

Range("A2,A4, A6:B10, A14:B14, A16:B16, A20:B20, A22:B22, A24:B24, A27:B27, A29:B29, A31:B31, A34:B34, A36:B36, A38:B38, A41:B41, A43:B43, A45:B45, A48:B48, A50:B50, A52:B52, A55:B55, A57:B57, A59:B59, A62:B62, A64:B64, A66:B66, A69:B69, A71:B71, A73:B73").ClearContents

Edit: What Dante said.

 

[RoryA]

To clarify, it's not the number of ranges, it's the number of characters in the text passed to Range, as Micron said.

You can also use Union to get round it:

Union(Range("A2,A4,A6:B10,A14:B14,A16:B16,A20:B20,A22:B22,A24:B24,A27:B27,A29:B29,A31:B31,A34:B34,A36:B36,A38:B38,A41:B41,A43:B43,A45:B45,A48:B48,A50:B50,A52:B52,A55:B55"), _
Range("A57:B57,A59:B59,A62:B62,A64:B64,A66:B66,A69:B69,A71:B71,A73:B73,A76:B76,A78:B78,A80:B80,A83:B83,A85:B85,A87:B87,A90:B90,A92:B92,A94:B94,A97:B97,A99:B99,A101:B101")).ClearContents

 

[willow1985]

Apparently it doesn't support that many ranges. I separated it into 2 lines:

Sub Clear1()
'
' Clear1 Macro
 
  Range("A2, A4, A6:B10,  A14:B14, A16:B16, A20:B20," & _
        "A22:B22, A24:B24, A27:B27, A29:B29, A31:B31," & _
        "A34:B34, A36:B36, A38:B38, A41:B41, A43:B43," & _
        "A45:B45, A48:B48, A50:B50, A52:B52, A55:B55").ClearContents
  Range("A57:B57, A59:B59, A62:B62, A64:B64, A66:B66," & _
        "A69:B69, A71:B71, A73:B73, A76:B76, A78:B78," & _
        "A80:B80, A83:B83, A85:B85, A87:B87, A90:B90," & _
        "A92:B92, A94:B94, A97:B97, A99:B99, A101:B101").ClearContents
 
  Dim o As Object
  For Each o In ActiveSheet.OLEObjects
     If InStr(1, o.Name, "CheckBox") > 0 Then
         o.Object.Value = False
     End If
  Next
 
  Range("A1").Select
End Sub

I did not know about the limit.
This worked perfect, Thank you!