Solved

# EXCEL: Random Select and Copy 10% of the Row

Posted on 2016-10-26
203 Views
I have been copying random 10% of entire row of data into the new sheet. For example, i have 1000 row of the data, i have to select and copy 10% row of data (which is 100) randomly into the new sheet. Been doing this on weekly basis.

Is there way to do it automatically by selecting and copying 10% of row into the new sheet using VBA Excel?. It would be great if you can create VBA macro code to detect the entire number of row automatically and select 10% of the row randomly.

I have attached mock spreadsheet attachment and you can use the example to random select 10% of the row into new sheet

Please let me know if you have issue.
SelectRandom.xlsx
0
Question by:Sherry
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3

LVL 22

Expert Comment

ID: 41861205
Check attached file.
Press button to run.

This is the code

``````Option Explicit

Sub Extract10Percent()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rwMax As Long, rw As Long

Application.ScreenUpdating = False

Set ws1 = Worksheets("Sheet1")
ws1.Range("A2").CurrentRegion.Copy ws2.Range("A1")
rwMax = ws2.Range("A1").CurrentRegion.Rows.Count
ws2.Range("D1") = "Random"
For rw = 2 To rwMax
ws2.Range("D" & rw) = WorksheetFunction.RandBetween(1, 10)
Next rw
rw = WorksheetFunction.RoundUp((rwMax - 1) / 10, 0) + 2
ws2.Range("D1:D" & rwMax).ClearContents
ws2.Range("A" & rw & ":C" & rwMax).ClearContents
End Sub
``````
SelectRandom.xlsm
0

Author Comment

ID: 41861309
Ejgil Hedegaard

Many thanks for the prompt solution, it has met my expectation entirely and very interesting coding. I have one more query.

If i have 30 column of data, is there way that your code that identify the last column instead of putting column "D" in your coding.

Just incase if i have come across with different number of column

Many thanks
0

Author Comment

ID: 41862589
Apologies another query, I have changed the number to 5 in the coding and it doesn't generate 5% number of alert.
0

LVL 22

Accepted Solution

Ejgil Hedegaard earned 500 total points
ID: 41862675
The values (1 to 10) in the RandBetween formula does not set the percentage, so changing 10 to 5 does not change the number of output rows.
The random values are not used to select the rows, so it can be anything (more or less), but is used to set the sorting values.
I think it will be better with more random values, so I have changed the interval to 1 to 1000.
The number of result rows is in the RoundUp formula.
Divide by 10 is equal to 10 %, changed to accept any percentage.
Code changed to accept any number of columns.
Set the percentage in cell K1, and run.
It is a named range (the name is used in the program), so you can move (not copy) the cell to another location.

``````Sub ExtractPercent()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rwMax As Long, rw As Long, colMax As Integer

Application.ScreenUpdating = False

Set ws1 = Worksheets("Sheet1")
ws1.Range("A2").CurrentRegion.Copy ws2.Range("A1")
rwMax = ws2.Range("A1").CurrentRegion.Rows.Count
colMax = ws2.Range("A1").CurrentRegion.Columns.Count + 1
ws2.Cells(1, colMax) = "Random"
For rw = 2 To rwMax
ws2.Cells(rw, colMax) = WorksheetFunction.RandBetween(1, 1000)
Next rw
rw = WorksheetFunction.RoundUp((rwMax - 1) * [ExtractPercentage], 0) + 2
ws2.Range(Cells(1, colMax), Cells(rwMax, colMax)).ClearContents
ws2.Range(Cells(rw, 1), Cells(rwMax, colMax)).ClearContents
ws2.Columns.AutoFit
End Sub
``````
SelectRandom.xlsm
0

Author Comment

ID: 41862839
Thanks Ejigil

That was smart coding but received coding error when i run them. see attached

Good idea on the custom percentage
Screen-Shot-2016-10-27-at-19.29.25.png
Screen-Shot-2016-10-27-at-19.32.57.png
0

LVL 22

Expert Comment

ID: 41863016
Guess you used the code in another workbook, where the named range ExtractPercentage is missing.
Name the cell with the percentage value, and it will work.
The name must be ExtractPercentage, or if you use something else, change the name in the brackets [ ] to whatever you name it.
0

Author Closing Comment

ID: 41869146
Excellent Answer and Interesting VBA coding. Expectation met
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as scriptâ€¦
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month3 days, 18 hours left to enroll