Module will not run

[ericaindallas]

I'm trying to run a somewhat generic VBA code from one of Mr. Jelen's books (VBA and Macros for MS Office Excel 2007). I'm trying to extract a unique list of values. [Besides using the "Remove Duplicates" function].

I have my dataset in Book1, Sheet1, beginning in cell A1.

Here is the code...

Sub GetUniqueCustomers()
Dim IRange As Range
Dim ORange As Range

'Find the size of today's dataset
FinalRow = Cells(Row.Count, 1).End(xlUp).Row
NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 2

'Set up output range. Copy heading from J1 there
Range("J1").Copy Destination:=Cells(1, NextCol)
Set ORange = Cells(1, NextCol)

'Define the Input Range
Set IRange = Range("A1").Resize(FinalRow, NextCol - 2)

'Do the Advanced Filter to get unique list of customers
IRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ORange, Unique:=True

End Sub

When I run this I get an error, "Run-time error '424': Object required".

Your help is appreciated!!

Thanks -Erica

 

[VoG]

Click Debug - which line of code is highlighted?

 

[xld]

You haven't specicied a criteria range in your advanced filter.

 

[ericaindallas]

Selected Debug and it highlights the first line.

Thx -E

 

[Norie]

Erica

Do you get any error message?

If you do can you post it, that might help us identify the problem.

xld

As far as I'm aware you don't need to specify a criteria range - it's an optional argument.

Obviously most of the time you would but if all the code is meant to do is get uniques it shouldn't be needed.

 

[Jon von der Heyden]

Hi Erica

Change:

FinalRow = Cells(Row.Count, 1).End(xlUp).Row

To:

FinalRow = Cells(Row[COLOR=Red][B]s[/B][/COLOR].Count, 1).End(xlUp).Row

 

[ericaindallas]

Thank you Jon! Yes, I left a letter out...


Thank you very much for catching that.

-Erica

 

[xld]

Erica

Do you get any error message?

If you do can you post it, that might help us identify the problem.

xld

As far as I'm aware you don't need to specify a criteria range - it's an optional argument.

Obviously most of the time you would but if all the code is meant to do is get uniques it shouldn't be needed.

I know Norie, but I was wondering what was her unique key without criteria. I guess it was the whole row (which to my mind is nonsense).

 

[Norie]

Good point, never thought of that.

Suppose I just assumed that it was the one column being filtered to get the unique values.