Chose random % of row from sheet1 and paste in to sheet2

[motilulla]

Using Excel 2010

Hello, I have sheet1 with numbers approximately 6000 to 9000 rows with 14 columns I want to pick random % of row and paste in to sheet2.

Please suggest a VBA solution. Thank you in advance.

Example sheet1 with 40 rows

Pick Random Rows.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		TT:Rows
2		40
3
4
5		S.N	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
6		1	8	58	5	77	5	3	40	76	24	75	53	40	23	65
7		2	39	72	58	64	60	69	41	47	63	52	65	24	80	22
8		3	26	30	14	35	77	54	68	24	67	9	79	66	49	80
9		4	28	52	49	28	37	27	65	66	43	19	62	72	13	42
10		5	21	41	73	78	10	9	19	80	53	36	8	67	40	47
11		6	24	19	62	15	80	48	13	17	78	41	52	29	58	49
12		7	19	71	44	16	33	35	30	33	76	76	3	10	36	52
13		8	12	8	62	64	72	4	66	62	67	42	4	20	27	47
14		9	12	68	39	31	33	58	76	71	39	75	15	19	23	32
15		10	48	19	54	7	24	32	61	22	53	65	10	43	2	57
16		11	15	56	74	79	32	45	40	2	13	70	24	32	47	44
17		12	75	48	12	42	52	3	25	19	6	34	15	17	18	52
18		13	51	55	11	2	3	54	14	50	19	28	30	63	62	56
19		14	17	13	47	31	76	52	58	25	74	48	70	79	37	55
20		15	80	23	43	59	9	3	79	46	21	68	61	44	24	15
21		16	43	38	23	16	35	28	65	43	67	41	69	60	39	75
22		17	70	37	37	76	36	62	4	61	8	44	10	58	23	24
23		18	52	5	64	54	59	70	1	20	45	9	55	4	71	12
24		19	52	38	15	73	6	58	2	45	74	53	32	50	52	44
25		20	54	51	50	69	61	15	42	25	23	73	77	31	6	24
26		21	65	1	47	77	59	59	27	42	4	66	8	50	37	48
27		22	72	68	57	79	57	14	75	25	60	49	16	33	68	29
28		23	42	80	23	70	19	77	10	26	78	46	55	23	44	73
29		24	9	69	74	35	61	64	26	23	78	5	76	19	9	37
30		25	28	32	37	52	31	16	29	78	15	80	8	65	73	11
31		26	55	80	66	70	64	55	6	53	23	50	36	74	57	3
32		27	78	52	38	64	35	61	9	26	39	62	59	4	49	2
33		28	60	70	17	77	36	64	77	7	79	18	76	67	77	33
34		29	41	72	59	7	15	11	11	53	50	30	51	66	4	57
35		30	42	80	71	39	17	35	45	37	54	69	29	57	25	6
36		31	38	27	5	76	76	36	57	67	1	15	57	9	39	69
37		32	63	48	24	4	14	52	30	33	32	51	36	65	31	60
38		33	60	80	36	55	75	68	14	32	41	24	78	41	7	1
39		34	52	22	76	28	52	34	73	59	51	77	68	51	35	78
40		35	14	49	53	50	43	26	13	28	61	11	52	74	62	78
41		36	13	36	75	8	60	74	18	16	80	54	73	63	31	28
42		37	8	31	24	78	1	77	9	64	80	61	24	19	24	78
43		38	20	6	74	48	1	76	21	51	30	66	73	30	31	11
44		39	42	39	13	49	2	79	56	69	53	13	1	42	63	12
45		40	26	9	9	6	8	16	66	2	16	56	62	58	26	52
46
47
Sheet1

Cell Formulas
Range	Formula
B2	B2	=COUNTA(B6:B45)

Random Result selection 10 % of rows = 4 pasted in to sheet2

Pick Random Rows.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		TT:Rows
2		4
3
4
5		S.N	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
6		4	28	52	49	28	37	27	65	66	43	19	62	72	13	42
7		13	51	55	11	2	3	54	14	50	19	28	30	63	62	56
8		23	42	80	23	70	19	77	10	26	78	46	55	23	44	73
9		37	8	31	24	78	1	77	9	64	80	61	24	19	24	78
10
11
12
13
14
Sheet2

Cell Formulas
Range	Formula
B2	B2	=COUNTA(B6:B45)

Regards,
Moti

 

[motilulla]

Here i summit the cross post link. Regards, Moti

Pick random rows and copy in to sheet2

Using Excel 2010 Hello, I have sheet1 with numbers approximately 6000 to 9000 rows with 14 columns I want to pick random % of row and paste in to sheet2. Please suggest a VBA solution. Thank you in advance. Example sheet1 with 40 rows Random selection 10 % of rows = 4 and result...

www.excelforum.com

 

[myall_blues]

I read this as you wanting two random numbers - first you want to pick a random number of rows from the total dataset, and then you want to select that number of random rows from the dataset. Is that correct?

Sub RandomPercent()
    Dim NumSelected As Long, TotalRows As Long, newRnd As Long, i As Long
    Dim inRng, outRng
    TotalRows = Sheets("Sheet1").Range("B2").Value
    inRng = Sheets("Sheet1").Range("B6:P" & TotalRows + 6 - 1)
    Randomize
    NumSelected = Int((TotalRows * Rnd) + 1)
    For i = 1 To NumSelected
        newRnd = Int((TotalRows * Rnd) + 1)
        outRng = Application.WorksheetFunction.Index(Sheets("Sheet1").Range("B6:P" & TotalRows + 6 - 1), newRnd, 0)
        Sheets("Sheet2").Range("B" & i + 5).Resize(1, 15) = outRng
    Next
End Sub

 

[motilulla]

I read this as you wanting two random numbers - first you want to pick a random number of rows from the total dataset, and then you want to select that number of random rows from the dataset. Is that correct?

Hello myall_blues, I wanted bit specific I can select any amount of % via input any way my request is solved via cross post. I appreciate your help.

Have a good day and Good Luck.

My Best Regards,
Moti