I am using some VBA macro code I found on this very board and then modified to fit my needs. I want it to pull random samplings of rows from a very long list (i.e. random 15% from 4000 rows). The macro is triggered by a button and is used across ~10 sheets in this particular workbook.
I am having a strange issue in that every once in a while the macro will randomly sample the header row. I attempted to modify the code to always start from the second row, but apparently I missed something. Can anyone see where I sent wrong here? Thanks in advance for your help!
I am having a strange issue in that every once in a while the macro will randomly sample the header row. I attempted to modify the code to always start from the second row, but apparently I missed something. Can anyone see where I sent wrong here? Thanks in advance for your help!
Code:
Sub PullRandom(FromSheet As Integer, Pct As Double)
Randomize
Dim SelRow As Range
Dim MyRows() As Integer
Dim numRows, percRows, nxtRow, nxtRnd, chkRnd, copyRow As Integer
numRows = Sheets(FromSheet).Range("A" & Rows.Count).End(xlUp).Row - 1
percRows = CInt(numRows * Pct)
ReDim MyRows(percRows)
For nxtRow = 2 To percRows
getNew:
nxtRnd = Int((numRows) * Rnd + 1)
For chkRnd = 1 To nxtRow
If MyRows(chkRnd) = nxtRnd Then GoTo getNew
Next
MyRows(nxtRow) = nxtRnd
Next
For copyRow = 2 To percRows
Sheets(FromSheet).Rows(MyRows(copyRow)).EntireRow.Copy _
Destination:=Sheets(7).Cells(copyRow, 1)
Next
Sheets(FromSheet).Range("A2:IV65536").Clear
For copyRow = 2 To percRows
Sheets(7).Rows(copyRow).EntireRow.Copy _
Destination:=Sheets(FromSheet).Cells(copyRow, 1)
Next
Sheets(7).Cells.Clear
Sheets(FromSheet).Rows("1:10000").RowHeight = 13.5
End Sub