VBA Error Message "Compile error: Argument not optional"

J

JStreck

New Member
Joined
Apr 14, 2010
Messages
26
I've recorded a Macro in which I select several ranges of cells within a worksheet to be cleared, clear them, then save. It seems straight forward enough, and I've done this multiple times before on other duplicate worksheets without issue; however, I've done this several times on this sheet, but every time results in the same error for the same line in the code for this particular worksheet.

I don't know enough about VBA (hense why I recorded the macro rather than writing it) to trouble shoot it. I could use some help. The macro is tied to a button, and appears when I click the button. Here's the code, and the line that is highlighted when the error pops up: Compile error: Argument not optional

Sub Sheet_Clear()
'
' Sheet_Clear Macro
' Resets all data entry cells back to zero. Macro created by J. Streck on 10/21/2010.
'
Dim nResult As Long
nResult = MsgBox( _
Prompt:="Are you sure you want to clear your sheet? Click YES to clear, or NO to stop.", _
Buttons:=vbYesNo)
If nResult = vbNo Then
Exit Sub
End If
Range("Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67").Select
Range("FQ67").Activate
Range( _
"Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85" _
).Select
Range("FQ85").Activate
Range( _
"Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103" _
).Select
Range("FQ103").Activate
Union(Range( _
"Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("FQ121").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119" _
), Range("CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121")).Select
Range("HI47").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103" _
), Range("BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121")).Select
Range("HQ47").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101" _
), Range( _
"EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("HA70").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,HI70:HJ83,HQ70:HR83,Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85" _
), Range( _
"BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("HQ70").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,HI70:HJ83,HQ70:HR83,FT88:FU101,GC88:GD101,GK88:GL101,GS88:GT101,HA88:HB101,Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67" _
), Range( _
"BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("HA88").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,HI70:HJ83,HQ70:HR83,FT88:FU101,GC88:GD101,GK88:GL101,GS88:GT101,HA88:HB101,HI88:HJ100,HI101:HJ101,HQ88:HR101,Q47:R65,BF47:BF65" _
), Range( _
"BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("HQ88").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,HI70:HJ83,HQ70:HR83,FT88:FU101,GC88:GD101,GK88:GL101,GS88:GT101,HA88:HB101,HI88:HJ100,HI101:HJ101,HQ88:HR101,FT106:FU119" _
), Range( _
"ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("GK106").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,HI70:HJ83,HQ70:HR83,FT88:FU101,GC88:GD101,GK88:GL101,GS88:GT101,HA88:HB101,HI88:HJ100,HI101:HJ101,HQ88:HR101,FT106:FU119" _
), Range( _
"CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("HQ106").Activate
Selection.ClearContents
Range("Q47").Select
ActiveWorkbook.Save
End Sub


Thanks for looking.

James
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Union requires at least two arguments (two ranges to union) and you only have one, and so don't need it.

Delete Union(...)
 
Upvote 0
That's what I thought, and I tried that before posting, but it then gives me another error. This is where my troubleshooting skills fall short. I don't know what all the errors mean, or what to do to correct them.

The part that gets me is that all I did was record a series of steps using the Record Macro function; Excel put in the Union bit. ??

Here's the newest error after removing the unneccessary Union bits as you recommended; Run-time error '1004': Method 'Range' of object '_Global' failed. I'm sure it's something simple, but I don't understand it.

Sub Sheet_Clear()
'
' Sheet_Clear Macro
' Clears all time and resets all data entry cells back to zero. Macro created by J. Streck on 10/21/2010.
'
Dim nResult As Long
nResult = MsgBox( _
Prompt:="Are you sure you want to clear your sheet? Click YES to clear, or NO to stop.", _
Buttons:=vbYesNo)
If nResult = vbNo Then
Exit Sub
End If
Range("Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67").Select
Range("FQ67").Activate
Range( _
"Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85" _
).Select
Range("FQ85").Activate
Range( _
"Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103" _
).Select
Range("FQ103").Activate
Range( _
"Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
).Select
Range("FQ121").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119" _
), Range("CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121")).Select
Range("HI47").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103" _
), Range("BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121")).Select
Range("HQ47").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101" _
), Range( _
"EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("HA70").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,HI70:HJ83,HQ70:HR83,Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85" _
), Range( _
"BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("HQ70").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,HI70:HJ83,HQ70:HR83,FT88:FU101,GC88:GD101,GK88:GL101,GS88:GT101,HA88:HB101,Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67,ED67" _
), Range( _
"BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("HA88").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,HI70:HJ83,HQ70:HR83,FT88:FU101,GC88:GD101,GK88:GL101,GS88:GT101,HA88:HB101,HI88:HJ100,HI101:HJ101,HQ88:HR101,Q47:R65,BF47:BF65" _
), Range( _
"BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("HQ88").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,HI70:HJ83,HQ70:HR83,FT88:FU101,GC88:GD101,GK88:GL101,GS88:GT101,HA88:HB101,HI88:HJ100,HI101:HJ101,HQ88:HR101,FT106:FU119" _
), Range( _
"ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("GK106").Activate
Union(Range( _
"FT47:FU65,GC47:GD65,GK47:GL65,GS47:GT65,HA47:HB65,HI47:HJ65,HQ47:HR65,FT70:FU83,GC70:GD83,GK70:GL83,GS70:GT82,GS83:GT83,HA70:HB83,HI70:HJ83,HQ70:HR83,FT88:FU101,GC88:GD101,GK88:GL101,GS88:GT101,HA88:HB101,HI88:HJ100,HI101:HJ101,HQ88:HR101,FT106:FU119" _
), Range( _
"CS47:CS65,EF47:EF65,BD67,CQ67,ED67,FQ67,Q70:R83,BF70:BF83,CS70:CS83,EF70:EF83,BD85,CQ85,ED85,FQ85,Q88:R101,BF88:BF101,CS88:CS101,EF88:EF101,BD103,CQ103,ED103,FQ103,Q106:R119,BF106:BF119,CS106:CS119,EF106:EF119,BD121,CQ121,ED121,FQ121" _
)).Select
Range("HQ106").Activate
Selection.ClearContents
Range("Q47").Select
ActiveWorkbook.Save
End Sub
 
Upvote 0
That's the trouble with recorded code. Its incredibly convulted and near impossible to read and therefore to troubleshoot.

Try the following:
1) select all your input cells
2) click in the field to the left of the formula bar
3) enter a name there (e.g. Inputs).
4) change your macro as follows:

Code: 
Sub Sheet_Clear()
'
' Sheet_Clear Macro
' Resets all data entry cells back to zero. Macro created by J. Streck on 10/21/2010.
'
If MsgBox( _
Prompt:="Are you sure you want to clear your sheet? Click YES to clear, or NO to stop.", _
Buttons:=vbYesNo) = vbYes Then Range("Inputs").ClearContents
End Sub
 
Upvote 0
Find the length (using the LEN() function) of the strings from which you want to form ranges.

That is Len("FT47:FU65,GC47:GD65, ... Q106:R119") etc. (dots in the middle put in to save space here, you would need the full string).

You're likely to get the error you report if the string length is greater than 255.

Suggested remedy if to split your long strings into shorter strings. This should just require having more strings in your UNION functions.
 
Upvote 0
JStreck:-

1) I just pasted your code into a module and it failed on the lines containing the following ranges: FQ10 3 and Q7 0:R83. Both contain embedded spaces and so did the subsequent lines, all in the same approximate positions in the line (128/256/384). You may want to check your code carefully for these embedded spaces. You're not using an external editor of any kind, are you?

Apart from activating several cells in turn and selecting an increasingly large series of ranges, 90% of your code does nothing - only the final Select actually does anything, i.e. select some cells to be cleared. All the other Selects just select cells then do nothing with them.

2) There's no point in using Union to join two ranges like this - and this is merely an example:-
Code: 
Range(Union("Q47:R65,BF47:BF65,CS47:CS65"),Range(",EF47:EF65,BD67,CQ67")).Select
If you know what the ranges are - and in this case you do - just do this:-
Code: 
Range("Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67").Select
In fact there's no need to Activate and Select anything: if you're going to clear something, just clear it:-
Code: 
Range("Q47:R65,BF47:BF65,CS47:CS65,EF47:EF65,BD67,CQ67").ClearContents

3) It's obvious that you've used the macro recorder to Ctrl-select a series of ranges and finally cleared them all at once - the recorder recorded your actions faithfully but not very intelligently. The recorder can generate usable code but its real power is to provide you with sample code which you can base your own code on - for example, if you've no idea how to code a sort or a filter, use the recorder and then customise the code it generates for your own application.

4) It makes code considerably more readable if you post it between CODE tags - the # icon in the editor toolbar: this switches to a fixed-width font and preserves indenting, both of which make it easier to follow. That in turn encourages people to take a look at it and you tend to get more responses.
 
Last edited:
Upvote 0
yytsunamiyy said:
That's the trouble with recorded code. Its incredibly convulted and near impossible to read and therefore to troubleshoot.

Try the following:
1) select all your input cells
2) click in the field to the left of the formula bar
3) enter a name there (e.g. Inputs).
4) change your macro as follows:

Code: 
Sub Sheet_Clear()
'
' Sheet_Clear Macro
' Resets all data entry cells back to zero. Macro created by J. Streck on 10/21/2010.
'
If MsgBox( _
Prompt:="Are you sure you want to clear your sheet? Click YES to clear, or NO to stop.", _
Buttons:=vbYesNo) = vbYes Then Range("Inputs").ClearContents
End Sub
Click to expand...

This made my day, and solved my problem! I very much appreciate your simple solution.

Ruddles - thank you for the tip regarding posting code...I'm sure it's in an FAQ that I missed somewhere. It's duely noted ;)

Thanks to everyone who took the time to look at this mess of a code and offer insight/suggestions/etc. to help me fix it.

James.
 
Upvote 0
Hi,

I am having the exact same problem as this, however, I am quite new to VBA. Are you able to tell me what you mean by "selecting the input Cells"? And which field to the left of the formula bar should I click on?

thanks a lot.
 
Upvote 0
RM76 said:
Are you able to tell me what you mean by "selecting the input Cells"?
Click to expand...

When you left-click on a cell, you are 'selecting' it. If you left-click on a cell and hold the mouse button down, then drag the mouse and release the mouse button, a number of cells are highlighted in grey: these are now 'selected'.

RM76 said:
And which field to the left of the formula bar should I click on?
Click to expand...

There's a blue-grey shape with a rounded end containing the letters fx to the left of the formula bar and a white box containing a drop-down arrow to the left of that. The white box.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,315
Messages
6,171,412
Members
452,399
Latest member
oranges

Share this page

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
Back
Top