0

I have an Excel Macro that clears certain cells in column F in my sheet. Here is the code that I am using.

 Sub ClearCells()
 Range("F1,F2,F5,F6,F9,F10,F13,F14,F17,F18,F21,F22,F25,F26,F29,F30,F33,F34,F37,F38,F41,F42,F45,F46,F49,F50,F53,F54,F57,F58,F61,F62,F65,F66,F69,F70,F73,F74,F77,F78,F81,F82,F85,F86,F89,F90,F93,F94,F97,F98,F101,F102,F105,F106,F109,F110,F113,F114,F117,F118,F121,F122,F125,F126,F129,F130,F133,F134,F137,F138,F141,F142").ClearContents
 End Sub

When I run it, I get this error. "Run-time error '1004': Method 'Range' of object '_global' failed"

It worked fine before I added F49-F142. The cells that I am trying to clear are not locked either so I'm not sure what else would be causing that error.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
joekeny
  • 15
  • 4
  • create an array of strings of the range addresses, that are less than 255 characters. Loop that array and clear them – Scott Craner Mar 01 '19 at 16:57
  • A physical line of VBA code cannot legally exceed 1023 characters, and IIRC a logical line of code cannot exceed 20 physical lines. The error you're getting has nothing to do with that though. – Mathieu Guindon Mar 01 '19 at 16:57
  • Technically what you have here is a range `Union`; you could have that long string of cells in a string, split it into an array, then iterate that array to `Union` the ranges, and invoke `ClearContents` on the union'd range. – Mathieu Guindon Mar 01 '19 at 17:01

2 Answers2

1

Use a loop:

Sub ClearCells()

Dim i as long

For i = 1 to 141 step 4
    Range("F" & i & ":F" & i + 1).clearcontents
Next i

End Sub
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
1

Since it seems you don't want to clear the lock cells in the range you describe, try the following:

For i = 1 To 141
    If Not Cells(i, 6).Locked = True Then
        Cells(i, 6).ClearContents
    End If
Next i

Please note: you could write If Not Cells(i, 6).Locked Then instead of If Not Cells(i, 6).Locked = True Then.

Pspl
  • 1,398
  • 12
  • 23
  • 1
    Nice catch! Consider using `Union` to keep it a single `ClearContents` call though - interacting with individual cells will negatively impact performance. – Mathieu Guindon Mar 01 '19 at 17:03