Redistributing Values in a Range to new Range

[Danny_]

Hi guys

Just before I run this one by you, I have to point out that I'm actually taking my first steps learning VBA, although been an excel standard user for a couple of years now. In fact, new to programming in general so you will have to excuse any dumb questions or statements of the obvious.

So, this I guess this is more of a sense check on my methodology than anything else.

The Task:
I have a variable length data range with 3 columns. Column A is a variable length key between 2 and 6 characters. Column B displays the level associated with the key (2 chars = lvl 1, 4 chars = lvl 2 etc.). Column C displays the Value/text associated with the key.
What I need to do with the range is redistribute each row into 3 separate ranges based on the length of the key (Column A). I have determined that I cannot qualify using column B as there are rogue key values e.g. I may have a key that has a length of 5, but will display as a valid column B level i.e. a key with length 5 will read as a level 3.

I have attached the file and you will see the macro I have created to try and tackle the task. Am I thinking along the right lines? Bear in mind that the live scenario will have a list of several thousand lines.
It may be that there is a particular VBA method for performing exactly this task, if there is, I'm not yet to that point

Next Steps:
Once I've pulled the data into the new ranges, there will be duplicates in each range so I need to remove those. Running a record macro, I can see there is a Range("***").RemoveDuplicates method, is this the most effective way to perform this task in VBA ?

Again, try not to forget I'm a VBA newbie, so please be gentle

Many thanks in advance

Danny_

[mike7952]

Danny

The code is really good for your first go of it. I dont really see a question. You can just add another case for the key with a length of 5. And you can record a macro for the dupes to remove and just slide it in at the bottom of your code.

[Elynth]

Or I guess you could check your newrange if it already contains the value and go for the next value if it does. But removing duplicates is really easy as mike said

[Danny_]

Great, thanks for the feedback guys.

You are right, it wasn't so much a question, more of a feedback request. As I'm starting out in VBA, I will probably post more of these just to reach out to the 'pro's' for feedback on my understanding of programming and programming techniques, I hope that's acceptable on this forum.

I'm now considering ways that I can actually remove the dupe's and I'm initially thinking that for each row I process, I can search the new range to check whether the value exists and skip pasting if it does, or I can let the process run and then do a RemoveDuplicates function for each of the ranges; the good point being that each of the new ranges will have a static anchor point.

I also plan to create a new range called "BAD KEYS" that will dump all key values that are not of one of the defined lengths e.g. 2,4,6. I will do this using a 'CASE ELSE' at the end of my select.

One of the things I am very guilty of as I start my VBA programming journey, is that I am being very critical of the amount of code I write. I tend to write 20 lines of code and then wonder to myself if actually 5 lines of 'smarter' code will do the same thing. I need to try to get into the habit of not worrying about the size of the code and if it at leasts performs the task in hand with 100 lines, I can then go back to it as I learn more techniques and functions etc. to see how I can streamline it.

Again, thanks guys, really appreciate the input.

Danny