I am simply trying to select a random row within a user selected range. However, at times the program will select a random row outside of the specific range. See code excerpt below:
Dim PopulationSelect As Range
Set PopulationSelect = Application.InputBox("Select range of cells in the population", Type:=8)
RandSample = Int(PopulationSelect.Rows.Count * Rnd +1)
Rows.(RandSample).EntireRow.Select
Does anyone know why this is, and how to fix it?
Thank you in advance.
It's because your random sample is going from 1 to the number of rows and your select function is working on the entire sheet.
So if populationSelect is A50:A51 your rand sample would be 1 or 2 and it would select row 1 or 2
Try
Sub test()
Dim PopulationSelect As Range
Set PopulationSelect = Application.InputBox("Select range of cells in the population", Type:=8)
RandSample = Int(PopulationSelect.Rows.Count * Rnd + 1)
PopulationSelect.Rows(RandSample).EntireRow.Select
End Sub
Consider:
Sub qwerty()
Dim PopulationSelect As Range
Set PopulationSelect = Application.InputBox("Select range of cells in the population", Type:=8)
Dim nLastRow As Long
Dim nFirstRow As Long
Set r = PopulationSelect
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
n = Application.WorksheetFunction.RandBetween(nFirstRow, nLastRow)
Cells(n, 1).EntireRow.Select
End Sub
