Please help me with making a Multidimensional Array

[learner99]

Hi,
I'm struggling to write a multidimensional array.
Could you please help?

Here is what I’m trying to do...

I have an excel sheet with the following data
Row 1 Column P 10,500
Row 2 Column P --------
Row 3 Column P 10,500
Row 4 Column P 6,000
Row 5 Column P --------
Row 6 Column P 6,000
Row 7 Column P 10,500

I wrote a loop to remove dupes but it only removes a dupe if the next cell is the same as the previous cell.

I would like it to ignore the "--------" and remove any rows that have a repeat value in any cell in column P.
So the output would look like this
Row 1 Column P 10,500
Row 2 Column P --------
Row 3 Column P 6,000
Row 4 Column P --------

Thank you in advance for your help.

Please Login or Register  to view this content.

[PCI]

To get it quickly can you send an XLS sample of the data

[Richard Buttrey]

Hi,

Always try and avoid VBA (and especially loopiing code) when there is perfectly straightforward Excel functionality available.
Filtering data is the fastest way I know of identifying data rows that need to be deleted. So in say Q1 enter

Please Login or Register  to view this content.

Copy it down and filter column Q for the word "Delete", then select all the filtered rows and delete them.

[learner99]

please see attached for a test fileTest file.xlsx

[learner99]

Thank you for your feedback. The logic I'm working on is just one step in a macro I'm building. I think a loop inside a loop is the only way to check each cell against one another to get ride of the dupes.

[PCI]

Try

Please Login or Register  to view this content.

[learner99]

Wow that's pretty cool!
The only questions I have are, how do I change the logic to delete the rows instead of just the cells? And can you put some comments in the logic so I better understand what's going on. I'm assuming ObjDic is a virtual table. I've never seen that command used before.
Thanks again!

[PCI]

The original purpose was "to write a multidimensional array" that was certainly why you were using "RowInfo()"
If you want to delete rows we can do it without the array: Please confirm.
About " put some comments " let's wait the final Procedure after the answer to the previous question
Concerning "ObjDic" it's an collection object, see for more detail:
http://www.snb-vba.eu/VBA_Dictionary_en.html (thank you snb)
http://www.experts-exchange.com/Soft...ss-in-VBA.html

[learner99]

Yes I would like to delete the rows that have duplicate cells. I'd also like to keep the array process incase I need to add another column to filter off of.
Hope that makes sense. And thank you for the links I will be checking them out.

[PCI]

See here after, of course some simplifications can be done versus the use of the array

Please Login or Register  to view this content.

[learner99]

That's perfect! Thank you.

[learner99]

Hey PCI, I hate to bother you again but I have to make what I think is a minor change to the code and for some reason Its not doing what I was expecting.
I had to add another column to check against.
What I'm looking to do is run the current loop until the data in column L changes. Basicly when cell 1 doesnt match the next cell down the loop would start over. This is so I don't delete repeats that are needed farther down the list.

I've attached test Data.
Here is the code after I added logic.

Please Login or Register  to view this content.

[PCI]

I'm not sure to get it right.
Previous macro is removing duplicate value, except when this value is "--------"
How this can be done only when value in column L is changing?
Can you send the final result you want from your file

[learner99]

Please see attached. The first tab is the input. Second tab is the output.
Thanks again for your help.

[PCI]

Give a chance to next code

Please Login or Register  to view this content.

[learner99]

Thank you for all the hard work on this! The code is way past my skill level. Can you add some comments so I can understand what's going on. I was thinking you would have use column L as a trigger to start the loop over when the number changed.

[PCI]

I will see to prepare you some explanations but not now, come back to me if in week there is still nothing sent.
PCI

[PCI]

See here code with comments
For DICTIONNARY info see
http://www.snb-vba.eu/VBA_Dictionary_en.html
Thanks to snb

Please Login or Register  to view this content.