ClearContent not working

[Wilfred_E]

I have the following code. It doesn't work. Says, "1004 Error", when implemented. Don't know what have done wrong.

Private Sub CommandButton1_Click()
Unload Me
ThisWorkbook.Sheets("Set Exams-Answers").Range("F21, H23:H26, F31, H33:H36, F41, H43:H46, F51, H53:H56, F61, H63:H66, F71, H73:H76, F81, H83:H86, F91, H93:H96, F101, H103:H106, F111, H113:H116, F121, H123:H126, F131, H133:H136, F141, H143:H146, F151, H153:H156, F161, H163:H166, F171, H173:H176, F181, H183:H186, F191, H193:H196, F201, H203:H206, F211, H213:H216, F221, H223:H226, F231, H233:H236, F241, H243:H246, F251, H253:H256, F261, H263:H266, P21, R23:R26, P31, R33:R36, P41, R43:R46, P51, R53:R56, P61, R63:R66, P71, R73:R76, P81, R83:R86, P91, R93:R96, P101, R103:R106, P111, R113:R116, P121, R123:R126, P131, R133:R136, P141, R143:R146, P151, R153:R156, P161, R163:R166, P171, R173:R176, P181, R183:R186, P191, R193:R196, P201, R203:R206, P211, R213:R216, P221, R223:R226, P231, R233:R236, P241, R243:R246, P251, R253:R256, P261, R263:R266").ClearContents 'Clear cell contents
SetQuestions1.Show
End Sub

Some, please help. Thank you.

 

[Logit]

You haven't indicated which portion of the macro is highlighted when the error appears.

I am suspecting it might be this portion :

 ThisWorkbook.Sheets("Set Exams-Answers").Range("F21, H23:H26, F31, H33:H36, F41, H43:H46, F51, H53:H56, F61, H63:H66, F71, H73:H76, F81, H83:H86, F91, H93:H96, F101, H103:H106, F111, H113:H116, F121, H123:H126, F131, H133:H136, F141, H143:H146, F151, H153:H156, F161, H163:H166, F171, H173:H176, F181, H183:H186, F191, H193:H196, F201, H203:H206, F211, H213:H216, F221, H223:H226, F231, H233:H236, F241, H243:H246, F251, H253:H256, F261, H263:H266, P21, R23:R26, P31, R33:R36, P41, R43:R46, P51, R53:R56, P61, R63:R66, P71, R73:R76, P81, R83:R86, P91, R93:R96, P101, R103:R106, P111, R113:R116, P121, R123:R126, P131, R133:R136, P141, R143:R146, P151, R153:R156, P161, R163:R166, P171, R173:R176, P181, R183:R186, P191, R193:R196, P201, R203:R206, P211, R213:R216, P221, R223:R226, P231, R233:R236, P241, R243:R246, P251, R253:R256, P261, R263:R266").ClearContents 'Clear cell contents

If that is the case, try reducing the number of cell references by 1/2. Then create another macro to clear the remaining cells. You can also check and re-check that entire portion of the macro to
insure all the commas and colons are correctly placed in all instances.

 

[dmt32]

Hi,
I suspect your code as written could be exceeding the Range Areas limit - maybe?

A workaround you can try is to break your ranges in to smaller chunks & then use Union to combine them & perform clearcontents

Private Sub CommandButton1_Click()
    Dim Rng(1 To 4) As Range

       With ThisWorkbook.Sheets("Set Exams-Answers")
                Set Rng(1) = .Range("F21, H23:H26, F31, H33:H36, F41, H43:H46, F51, H53:H56," & _
                                   "F61, H63:H66, F71, H73:H76, F81, H83:H86, F91, H93:H96, F101," & _
                                    "H103:H106, F111, H113:H116, F121, H123:H126, F131, H133:H136," & _
                                    "F141, H143:H146, F151, H153:H156, F161, H163:H166, F171, H173:H176,F181")
                                                
                Set Rng(2) = .Range("H183:H186, F191, H193:H196, F201, H203:H206, F211, H213:H216, F221," & _
                                    "H223:H226, F231, H233:H236, F241, H243:H246, F251, H253:H256, F261, H263:H266," & _
                                    "P21, R23:R26, P31, R33:R36, P41, R43:R46, P51, R53:R56, P61, R63:R66, P71, R73:R76," & _
                                    "P81, R83:R86, P91, R93:R96")
                                    
                Set Rng(3) = .Range("P101, R103:R106, P111, R113:R116, P121, R123:R126, P131,R133:R136, P141, R143:R146," & _
                                     "P151, R153:R156, P161, R163:R166, P171, R173:R176, P181,R183:R186, P191")

                Set Rng(4) = .Range("R193:R196, P201 , R203: R206 , P211, R213: R216 , P221, R223: R226 , P231," & _
                                    "R233: R236 , P241, R243: R246,P251, R253:R256, P261, R263:R266")
      End With
      
      Union(Rng(1), Rng(2), Rng(3), Rng(4)).ClearContents
                                
Unload Me
SetQuestions1.Show
End Sub

You will need to check that I have not missed out any ranges when splitting your code


Dave

 

[Wilfred_E]

You haven't indicated which portion of the macro is highlighted when the error appears.

I am suspecting it might be this portion :

 ThisWorkbook.Sheets("Set Exams-Answers").Range("F21, H23:H26, F31, H33:H36, F41, H43:H46, F51, H53:H56, F61, H63:H66, F71, H73:H76, F81, H83:H86, F91, H93:H96, F101, H103:H106, F111, H113:H116, F121, H123:H126, F131, H133:H136, F141, H143:H146, F151, H153:H156, F161, H163:H166, F171, H173:H176, F181, H183:H186, F191, H193:H196, F201, H203:H206, F211, H213:H216, F221, H223:H226, F231, H233:H236, F241, H243:H246, F251, H253:H256, F261, H263:H266, P21, R23:R26, P31, R33:R36, P41, R43:R46, P51, R53:R56, P61, R63:R66, P71, R73:R76, P81, R83:R86, P91, R93:R96, P101, R103:R106, P111, R113:R116, P121, R123:R126, P131, R133:R136, P141, R143:R146, P151, R153:R156, P161, R163:R166, P171, R173:R176, P181, R183:R186, P191, R193:R196, P201, R203:R206, P211, R213:R216, P221, R223:R226, P231, R233:R236, P241, R243:R246, P251, R253:R256, P261, R263:R266").ClearContents 'Clear cell contents

If that is the case, try reducing the number of cell references by 1/2. Then create another macro to clear the remaining cells. You can also check and re-check that entire portion of the macro to
insure all the commas and colons are correctly placed in all instances.

You guessed right! I obviously exceeded the cell limit. Breaking them apart by half solved the problem. Thank you very much!

 

[Wilfred_E]

Hi,
I suspect your code as written could be exceeding the Range Areas limit - maybe?

A workaround you can try is to break your ranges in to smaller chunks & then use Union to combine them & perform clearcontents

Private Sub CommandButton1_Click()
    Dim Rng(1 To 4) As Range

       With ThisWorkbook.Sheets("Set Exams-Answers")
                Set Rng(1) = .Range("F21, H23:H26, F31, H33:H36, F41, H43:H46, F51, H53:H56," & _
                                   "F61, H63:H66, F71, H73:H76, F81, H83:H86, F91, H93:H96, F101," & _
                                    "H103:H106, F111, H113:H116, F121, H123:H126, F131, H133:H136," & _
                                    "F141, H143:H146, F151, H153:H156, F161, H163:H166, F171, H173:H176,F181")
                                               
                Set Rng(2) = .Range("H183:H186, F191, H193:H196, F201, H203:H206, F211, H213:H216, F221," & _
                                    "H223:H226, F231, H233:H236, F241, H243:H246, F251, H253:H256, F261, H263:H266," & _
                                    "P21, R23:R26, P31, R33:R36, P41, R43:R46, P51, R53:R56, P61, R63:R66, P71, R73:R76," & _
                                    "P81, R83:R86, P91, R93:R96")
                                   
                Set Rng(3) = .Range("P101, R103:R106, P111, R113:R116, P121, R123:R126, P131,R133:R136, P141, R143:R146," & _
                                     "P151, R153:R156, P161, R163:R166, P171, R173:R176, P181,R183:R186, P191")

                Set Rng(4) = .Range("R193:R196, P201 , R203: R206 , P211, R213: R216 , P221, R223: R226 , P231," & _
                                    "R233: R236 , P241, R243: R246,P251, R253:R256, P261, R263:R266")
      End With
     
      Union(Rng(1), Rng(2), Rng(3), Rng(4)).ClearContents
                               
Unload Me
SetQuestions1.Show
End Sub

You will need to check that I have not missed out any ranges when splitting your code


Dave

Thank you, Dave! This works perfectly too. Would be engaging this due to the structure. Thanks

 

[Logit]

Glad you got it worked out.