copy random rows

[leader2k]

Hi
How to copy random rows without duplicates from one sheet to another in vba?
your help will be appreciated

 

[Nebu]

Are you talking about removing duplicates form a data set?

 

[leader2k]

Are you talking about removing duplicates form a data set?

Hi
No i have a sheet with 6 columns of data and i want to randomly choose some rows and copy them to another sheet .
My regards

 

[Somendra Misra]

Hi,

Can you tell your data layout with some examples of duplicates in it. And what would be result if user select rows with duplicates?

Regards,

 

[leader2k]

Hi,

Can you tell your data layout with some examples of duplicates in it. And what would be result if user select rows with duplicates?

Regards,

Hi
i have a sheet with 6 columns (A1:F3000) when i hit a command button it copy 50 rows randomly ,but i end up having duplicate rows.i use the code bellow .

[/Private Sub CommandButton1_Click()
Dim rndRow As Long, i As Integer

Rows(1).Copy Sheets("Sheet2").Range("A1")
For i = 1 To 50
rndRow = Int(Selection.CurrentRegion.Rows.Count * Rnd + 1)
Rows(rndRow).EntireRow.Copy Sheets("Sheet2").Range("A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1)
Next i
End Sub]

thanx,
My regards

 

[shrivallabha]

You will have to add check like below and handle.

Sub Get50RandomRows()
Dim lRowNum(49) As Long, lCnt As Long, lrow As Long, i As Long
Do While lCnt < 50
  lrow = Int((3000 - 2 + 1) * Rnd + 2)
  If Not IsNumeric(Application.Match(lrow, lRowNum, 0)) Then
  lRowNum(lCnt) = lrow
  lCnt = lCnt + 1
  End If
Loop
Rows(1).Copy Sheets("Sheet2").Range("A1")
For i = LBound(lRowNum) To UBound(lRowNum)
  Rows(lRowNum(i)).EntireRow.Copy Sheets("Sheet2").Range("A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1)
Next i
End Sub

 

[leader2k]

Thanks a lot,i appreciate the help i will try it today and let you know.
My regards