Extract unique values excluding blank cells

[Navin Agrawal]

I am seeking help with a simple VBA code

Scenario

Column header is in cell Q4

Data is in Q5:Q204 (including multiple blank cells)

I want the unique values listed downwards from AH5 with the same column header in AH4... Blank Cells in Q5:Q204 should be excluded when extracting the unique values in column AH...

If the unique values in AH can be sorted ascending... even better

Thanks in advance

[Trebor76]

Hi Navin,

Try this:

Please Login or Register  to view this content.

Regards,

Robert

[Navin Agrawal]

Hi Navin,

Try this:

Please Login or Register  to view this content.

Regards,

Robert

Thanks Robert... It returns an error... attaching a spreadsheet

[Navin Agrawal]

Hi Navin,

Try this:

Please Login or Register  to view this content.

Regards,

Robert

Hello Robert,

The solution suggested by you is returning an error...

My apologies for attaching an incomplete sample sheet... Attached is the correct sheet....

[Trebor76]

The solution suggested by you is returning an error...

That's odd - it works for me At a guess I'd say you're running the macro not from the sheet with the data and so here's a slight variation of my original code to accommodate for that:

Please Login or Register  to view this content.

If it still errors out I need to what line and what error details (number and description) you are getting to be able to help further.

Thanks,

Robert

[Navin Agrawal]

That's odd - it works for me At a guess I'd say you're running the macro not from the sheet with the data and so here's a slight variation of my original code to accommodate for that:

Please Login or Register  to view this content.

If it still errors out I need to what line and what error details (number and description) you are getting to be able to help further.

Thanks,

Robert

Many thanks Robert... This works... just a couple of things not happening...

This Macro is pasting the unique values.... but distorting the fonts & formatting...

The range AI6:AP204 is not being cleared before pasting the formula in AI5:AP5 in the rows below, based on the number of unique values.... thus the formulas in the extra rows continue to be there...

The graphs not happening...

Attaching the sheet again.... If you can help... it will be a big, big favor...

Many Thanks once again...

[jindon]

Different method.

Please Login or Register  to view this content.

[Trebor76]

I have put the following code here as a way for others to use it if they can't download it and also on the attached workbook as a way prove it also works without issue:

Please Login or Register  to view this content.

[Navin Agrawal]

I have put the following code here as a way for others to use it if they can't download it and also on the attached workbook as a way prove it also works without issue:

Please Login or Register  to view this content.

Many many many thanks Robert.... God bless you...

Would like to point out a couple of things...

1) The first graph on the sheets GRAPHS is an image and not an excel ... so it is not getting updated....

2) The legends in the pie chart are over-lapping the graph if the number of items increases... how do we shift the pie chart to the left corner?

Attaching your file again for reference...

what is the difference between .xlsm and .xlsb format?

Many thanks again...

[Trebor76]

1) The first graph on the sheets GRAPHS is an image and not an excel ... so it is not getting updated....

Note sure how that happened but it's a chart now

2) The legends in the pie chart are over-lapping the graph if the number of items increases... how do we shift the pie chart to the left corner?

Just click and drag the chart(s) to where ever you each them on the tab (I moved the second chart to be beside the first). You also size them this way as well.

what is the difference between .xlsm and .xlsb format?

xlsb files are smaller and Excel tends to run these files faster. Their downside is that they are not as compatible with other applications. There's plenty of articles about these different file types on the net.

[Navin Agrawal]

Note sure how that happened but it's a chart now



Just click and drag the chart(s) to where ever you each them on the tab (I moved the second chart to be beside the first). You also size them this way as well.



xlsb files are smaller and Excel tends to run these files faster. Their downside is that they are not as compatible with other applications. There's plenty of articles about these different file types on the net.

Thank you very much Robert... Appreciate your help... I am closing this thread and rating you / recommending you with highest stars / numbers

[Trebor76]

You're welcome and thank you