CODEWITHSUNDEEP
vbaexceluser-input
Kyle Snell
Kyle Snell

Reputation: 81

VBA Select Random Row In Range of Select

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.

Upvotes: 2

Views: 13842

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 0

gtwebb
gtwebb

Reputation: 3011

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

Upvotes: 6

Related Questions