|The code is not looking for duplicates of your data, it it looking for duplicate numbers when building the array of random Row numbers to move.|
Let's say you have 100 rows of data (numRows) and that you want to move a random set of 10 of those rows. (percRows = 10)
The section that builds the array of row numbers (MyRows) will loop 10 times:
'Create Random numbers and fill array
For nxtRow = 1 To percRows
Each time through the loop, this instruction will generate a random number (nxtRnd) between 1 and 100 (numRows):
'Generate Random number
nxtRnd = Int((numRows) * Rnd + 1)
If we let the code simply generate 10 random numbers from 1 to 100, we could end up with an array that looks like this, which is really only 8 rows since 5 appears 3 times.
(12, 5, 7, 5, 24, 5, 78, 100, 2, 10)
To avoid this unpleasant situation, the code loops through the then-current array (which grows by 1 element each time through the "building" loop) after each a random number is created and checks to see if the number already exists. If a duplicate is found, the code bounces back up to getNew and creates a new random number, which it then checks against the existing elements in the array. This continues until no match is found.
'Loop through array, checking for Duplicates
For chkRnd = 1 To nxtRow
'Get new number if Duplicate is found
'If MyRows(chkRnd) = nxtRnd Then GoTo getNew
As soon as the code determines that the latest number (nxtRnd) doesn't already exist, it adds it to the array and goes back to the start of the build loop to create a new number.
'Add element if Random number is unique
MyRows(nxtRow) = nxtRnd
Once the array contains a set of 10 unique numbers... e.g.
(12, 5, 7, 61, 24, 85, 78, 100, 2, 10)
...then the code loops through the array and moves the rows designated by each element in the array.
'Loop through Array, copying rows to Sheet2
For copyRow = 1 To percRows
A few other things to consider:
1 - That last loop, which does the actual Cutting and Pasting uses the word "copy", when in reality it should be "cut" since that is what the instruction does. e.g. copyRow should become cutRow just to make the code clearer. It's just a variable name, so it won't make any difference in what the code does, it just makes reading the code a little easier since the variable name matches what is really being done.
2 - The same situation exist for the variable name "percRows". This was chosen because the original code was moving a percentage of the overall rows, not a fixed number. I would consider changing that variable name to something that matches what you are doing now, like maybe myNumRows or something like that.
3 - In all cases, you should make sure that you edit the comments for any code sections that you change to avoid confusion in the future. I find it very annoying when I am trying to figure out what a piece of code does and the comments don't match the instructions.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.