Filter uniques and ignore empty cells

Hi folks!

As most of you know I'm not a programmer.

I saw some code that uses advanced filter to extract unique entries to another location so I tried adding to it. Here's what I've come up with:

Please Login or Register  to view this content.

If the data in column A is not in a contiguous range the filter procedure considers empty cells a unique entry and includes the empty cell in the extraction range.

My question is: how to get this to ignore empty cells?

I am not a programmer too but as this codes reminds something to me a simple approach could be this.

Please Login or Register  to view this content.

That seems to work OK.

I'm wondering if this would be a good/practical alternative to using formulas to extract the uniques.

Thanks!

When I try this I get run time error 438, object doesn't support this property or method.

I was looking for an event macro to do this to make it dynamic as an efficient alternative to using formulas to extract the uniques.

Thanks!

Originally Posted by fredlo2010

which code are you using Tony?

Yours worked and Fotis' generated the error.

I'll play around with that tomorrow when I have more time.

Thanks!

Another way with scripting dictionary:

Please Login or Register  to view this content.

or, alternatively (slower):

Please Login or Register  to view this content.

All best,

berlan

..Tony Valko
which code are you using Tony?

Yours worked and Fotis' generated the error.

As i told i am not a programmer and i am sure that all the others codes are much better than mine. I just replied here because the code for unique list using Advanced Filter in another thread- that you told about-was mine.

But i know very well that my suggestion gives no error!

http://screencast.com/t/kYb3XR67

@ fredlo2010

Thanks for the rep and your kind comment!

Fotis' version will require Excel 2007 or later version - it seems Tony Valko may use Excel 2002 version also, where it will not work.

Yes, I use Excel 2002 as my default version so I tried Fotis' code in that version.

Just tried it in Excel 2007 and it worked but it included the column header as data and sorted it into the real data.

For 2002 version (and later) Fotis' code may be:

Please Login or Register  to view this content.

..Just tried it in Excel 2007 and it worked but it included the column header as data and sorted it into the real data.

No this is not corect. I attached a sample workbook that shows that does not exists such a problem and also a video that also shows that there is not such a problem.

You insist just to say that this does not works for some reason. Unfortunately i can not proove that i am not an elephant!

Good luck.

@Fotis

Your code will not work in 2003 or previous version because Sort object does not exist so error 438 is raised. I do not understand comment about elephants?

Originally Posted by Izandol

@Fotis

Your code will not work in 2003 or previous version because Sort object does not exist so error 438 is raised. I do not understand comment about elephants?

I replied to this comment of Tony Valko.

..Just tried it in Excel 2007 and it worked but it included the column header as data and sorted it into the real data.

I have reason to don't accept your explanations for Excel 2003.

My English are not so good so i'll try to explain my comment for Elephant...

If someone tell me: "You are an Elephant and not a human". How can i proove to him that i am not?

I'll show a picture of me and of many other people. If he insists that i am an Elephant, then i have no other way to proove that i am not!

I hope to be more clear now.

Originally Posted by Fotis1991

You insist just to say that this does not works for some reason.

Because it doesn't work in my versions of Excel.

Here's a sample file done in Excel 2007. Note how the column header is sorted into the data.

No need to get upset.

Originally Posted by Fotis1991

I have reason to don't accept your explanations for Excel 2003.

What is reason?

Code in your posted workbook is not same code as in your first reply, which uses different ranges and has changed the line:

Please Login or Register  to view this content.

for:

Please Login or Register  to view this content.

Apologize!! My English....!

I wanted to say that

I have no reason to don't accept your explanations for Excel 2003.

Originally Posted by Tony Valko

Because it doesn't work in my versions of Excel.

Here's a sample file done in Excel 2007. Note how the column header is sorted into the data.

No need to get upset.

You forgot the attachment.

It's not the problem if i am or not upset.And i am not. I am just too busyat this time but also i'd like to clear this issue. The problem is-and you know better than me as you are in the forums longer time than me-when the op just says:"This Does not works".

Originally Posted by Fotis1991

Apologize!! My English....!

I wanted to say that

I have no reason to don't accept your explanations for Excel 2003.

All is clear now.

Originally Posted by Fotis1991

You forgot the attachment.

I am just too busyat this time...

Yeah, me too! That's why I forgot to post the file.

Here it is:

Fotis(1).xlsm

To attach a sample sheet in our forum.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

In post#11 i uploaded a sample workbook. There were 2 modules with 2-a little-differents macros. The ClickMe button was connected with the macro called "Sub ÌáêñïåíôïëÞ5()"--YEs it's Greek..!" and works great.

In your example sheet you use the macro called "MACRO1" and this gives you wrong results.

Use the correct code and everything will be ok:

Please Login or Register  to view this content.

and works fine.

Originally Posted by Fotis1991

In your example sheet you use the macro called "MACRO1" and this gives you wrong results.

MACRO1 is the code you posted in reply #3.

Use the correct code and everything will be ok:

Please Login or Register  to view this content.

and works fine.

Using the code above in Excel 2007 I get duplicated results.


Data Range

	A	B	C	D
1	Header	-----	-----
2	1			1
3	1			1
4				2
5				a
6	2			x
7	1
8	x
9
10	2
11
12	x
13	a

How about

Please Login or Register  to view this content.

@ jindon...

I finally found some time to try your suggestion in reply #8.

Worked as expected and was relatively "fast" on large amounts of data.

On the initial calculation it took about 3 seconds to extract 101 unique items from a range of 10,000 rows.

Thanks!

I'll try the other suggestions when I get enough time to test them. I'll let everyone know how they work.

Dictionary is not available on Mac but there are many hash table-type classes that may be used in place.

If most poster knows this why ask the question if it will work on Mac?

Oh I am sorry - I did not see berlan posted code with Dictionary and not you. I understand and apologise.

Evaluate will work on Mac, by the way.

@ berlan

Just tested your code in reply #10 that uses the scripting dictionary.

This worked as expected and is very fast!

It took about 1 second to extract 101 unique entries from 10,000 rows of data.

Thanks!

@ jindon

Yes, that version is also very fast. Less than 1 second to extract the 101 unique entries from 10,000 rows of data.

Thanks!