Macro To Copy Random Rows

Microsoft Excel 2010 - complete product...
November 30, 2011 at 07:04:15
Specs: Windows 7
I need a Macro To Copy Random Rows from one sheet to another.

A post by DerbyDad03 was giving the answer but I don't understand how to make it work in my case. I copy the text in my Macro and nothing seems to happen...

Anyone can help ?

Here is the solution given by DerbyDad03 :

Option Explicit
Sub Random20()
Randomize 'Initialize Random number seed
Dim MyRows() As Integer ' Declare dynamic array.
Dim numRows, percRows, nxtRow, nxtRnd, chkRnd, copyRow As Integer
'Determine Number of Rows in Sheet1 Column A
numRows = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Get 20% of that number
percRows = numRows * 0.2
'Allocate elements in Array
ReDim MyRows(percRows)
'Create Random numbers and fill array
For nxtRow = 1 To percRows
getNew:
'Generate Random number
nxtRnd = Int((numRows) * Rnd + 1)
'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
Next
'Add element if Random number is unique
MyRows(nxtRow) = nxtRnd
Next
'Loop through Array, copying rows to Sheet2
For copyRow = 1 To percRows
Sheets(1).Rows(MyRows(copyRow)).EntireRow.Copy _
Destination:=Sheets(2).Cells(copyRow, 1)
Next
End Sub

Thank you all


See More: Macro To Copy Random Rows

Report •


#1
November 30, 2011 at 08:20:19
First, before you post any more data or code in this forum, please click on the blue line at the end of this post and read the instructions found via that link.

Second...

re: "I copy the text in my Macro and nothing seems to happen"

I don't know what you mean by "I copy the text in my Macro".

What do you mean by "my Macro"?

Where are you copying the code to?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 30, 2011 at 08:39:09
Thank you.
Sorry, I was wondering how to post my code and didn't know about the pre tag.

I copied the code in the VBAproject of my Excel file, in the Modules folder.
Is this clear enough ? Do you need a print screen ?


Report •

#3
November 30, 2011 at 08:51:36
Do you have data in Sheet 1 Column A?

If yes, did you run the code?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 30, 2011 at 09:07:38
Yes.
I have code in Sheet 1 and I ran the code several times without any result...


Report •

#5
November 30, 2011 at 10:00:54
re: "I have code in Sheet 1"

That's not what I asked.

I asked: "Do you have data in Sheet 1 Column A?" I am asking about the data in the spreadsheet, not about the macro.

The basic concept of the code is as follows:

1 - Determine how many rows of data are in Sheet 1 Column A, e.g. 100 rows.
2 - Calculate 20% of that number, e.g. 100 rows of data means that 20 rows will get copied.
3 - Randomly select e.g. 20 unique numbers between 1 and 100, inclusive, and store them in an array within VBA's memory.
4 - Loop through the array, copying the rows represented by the e.g. 20 random numbers from Sheet1 to Sheet 2.

I just entered data into Sheet1!A1:A100 and ran the code that you posted. It copied 20 unique, random rows from Sheet 1 to Sheet 2 as expected.

I then put data into Sheet1A1:A1000 and it copied 200 unique, random rows from Sheet 1 to Sheet 2.

Works just fine for me.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 1, 2011 at 01:06:34
Thank you for your help.
It's working now.

Report •


Ask Question