ericaindallas

New Member
Joined
Aug 20, 2009
Messages
45
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
:eek:
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Click Debug - which line of code is highlighted?
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
You haven't specicied a criteria range in your advanced filter.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,810
Office Version
  1. 365
Platform
  1. Windows
Hi Erica

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

ericaindallas

New Member
Joined
Aug 20, 2009
Messages
45

ADVERTISEMENT

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


Thank you very much for catching that.

-Erica
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Good point, never thought of that.

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

Forum statistics

Threads
1,141,284
Messages
5,705,510
Members
421,399
Latest member
hjweiss00

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
Top