#5 Invalid procedure call or argument

bobkap

Active Member
Joined
Nov 22, 2009
Messages
313
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I cannot get past this line of code. I keep getting this invalid message. If I just use something like "Range("$A$1:$h$20")" I don't get the error and the macro runs fine. But, I want to use variables as my range is going to vary every time this macro is run. Any help would be greatly appreciated.

VBA Code:
Range(Cells(1, 1), Cells(finalrow, finalcol)).RemoveDuplicates Columns:=Array(email, courseyear), Header:=xlYes
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
How have you declared you variables, and what are their values?
 
Upvote 0
Thanks for asking. Here are my dim statements.
VBA Code:
Dim startdate As Date
Dim enddate As Date
Dim curdate As Variant
Dim yearnum As Variant
Dim finalrow As Integer
Dim finalcol As Integer
Dim courseyear As Integer
Dim Wks As Worksheet: Set Wks = ActiveWorkbook.ActiveSheet
 
Upvote 0
What are the values of finalrow and finalcol? You have declared them but there is nothing to show that they have any kind of value.
 
Upvote 0
Thanks again.
I just checked they do have values. Here's the lines I use for that.
VBA Code:
finalcol = Cells(1, Columns.Count).End(xlToLeft).Column
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
 
Upvote 0
Finalcol is 7; Finalrow is 130
 
Upvote 0
Just to check that you got those values by hovering your mouse over the variables in the line that is showing the error?
 
Upvote 0
That's right. I ran the code through those 2 variables then hovered over them to see what their numbers were.
 
Upvote 0
I can't see any issue with that half of the code.
What are the variables email and courseyear showing?
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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