random selection of rows

[Bradly]

I have a master list of all of our cases--there are approximately 20000 rows,
each row representing a single case, and each row has 13 columns of data. I
filtered the list, for example, to see all of the active cases for a case
manager. Suppose that filter produces 117 cases (there are of course a
different number for each case manager). I want to create a macro or use a
formula to randomly select one of these rows as a way to randomly select
cases for audits.

Is there a way to do this?
Thanks.

 

[OssieMac]

Hi Bradley,

The following code makes a random selection from the filtered (visible) data
and copies the row of data to Sheet2. If you change the filter and run it
again then the next row of data on sheet2 is placed under the previous row. I
have not included headers on sheet2 but I assume you will copy the headers
from Sheet1.

It is possible to run the code in a loop and automatically setting the
filters to each of the Managers in turn and creating the output on Sheet2. If
you want it done that way then let me know which column contains the changing
filters.

I have given you a code option between the asterisk lines of just selecting
the row instead of copying to Sheet2. Just comment out the Copy and Paste
code and uncomment the select line.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Sub RandomSelection()

'RandBetween function in Code
'requires Analysis ToolPak AddIn
'to be loaded.

'See Help for how to load
'Analysis ToolPak.

Dim rngCol As Range
Dim lngCells As Long
Dim lngRandom As Long
Dim cel As Range
Dim i As Long
'Edit "Sheet1" to your sheet name
With Sheets("Sheet1")
If .FilterMode Then
With .AutoFilter.Range
Set rngCol = .Columns(1) _
.Offset(1, 0) _
.Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
End With
Else
MsgBox "Filters not set." & vbLf & _
"Processing terminated."
Exit Sub
End If
End With

lngCells = rngCol.Cells.Count
lngRandom = WorksheetFunction _
.RandBetween(1, lngCells)

i = 0
For Each cel In rngCol
i = i + 1
If i = lngRandom Then

'Copy row of data and Paste to Sheet2
'Edit "Sheet2" to your output sheet name.
Range(cel, cel.Offset(0, 12)).Copy _
Destination:=Sheets("Sheet2") _
.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0)

'************************************
'Alternative to Copy and Paste above
'Just select data.
'Range(cel, cel.Offset(0, 12)).Select
'************************************
Exit For
End If
Next cel

End Sub

 

[Bradly]

I tried the code and added the Analysis ToolPak as you asked, but I get this
error message:

"Run-time error '438':
Object doesn't support this property or method" and the following code is
highlighted:

lngRandom = WorksheetFunction _
.RandBetween(1, lngCells)

Have I typed something in wrong?

 

[OssieMac]

Hi Bradley,

There is Analysis ToolPak and Analysis ToolPak - VBA. I think you need both.
However, if still won't work then replace the following code

lngRandom = WorksheetFunction _
.RandBetween(1, lngCells)

with these 2 lines of code

Randomize
lngRandom = Int((lngCells * Rnd) + 1)

 

[Chip Pearson]

There is Analysis ToolPak and Analysis ToolPak - VBA. I think you need both.

However, if still won't work then replace the following code

Because RandBetween is not a native Excel function (in 2003 and
earlier), it is not going to be found under WorksheetFunction. If you
have the ATP VBA reference (you don't need both ATP references, just
the VBA one), you can call RandBetween as if it were a native VBA
function. Note that you must have the add-in loaded and your project
must reference the atpvbaen.xla library.

L = RandBetween(1, 100)

When I use functions from another library, I like to qualify the name
of the function with the library name, just to keep things clear and
well documented.

L = [atpvbaen.xls].RandBetween(1, 100)

The [ ] chars are required because the library name contains a period
(and yes, the referenced library is "xls" not "xla").

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[OssieMac]

Thanks Chip. I've now tested in xl2002. I was not aware that it did not work
in earlier versions of xl.

To Bradly,

Just to make it clear what you need to do.
In the Worksheet you need the Add-In Analysis ToolPak - VBA.
In the VBA Editor select menu item Tools -> References and check the box
against atpvbean.xls. (Ensure you check the box; not just select the line)
and then OK.

Then your code is as follows.

lngRandom = [atpvbaen.xls] _
.RandBetween(1, lngCells)

or you can leave out [atpvbaen.xls]. as follows. (However, I do like Chip's
suggestion to include it because it provides documentation.)

lngRandom = RandBetween(1, lngCells)


or you can use the alternative code I gave you as follows and you then do
not need Analysis ToolPak or the Reference in VBA. Probably the better
solution.

Randomize
lngRandom = Int((lngCells * Rnd) + 1)


--
Regards,

OssieMac

There is Analysis ToolPak and Analysis ToolPak - VBA. I think you need both.
However, if still won't work then replace the following code

Because RandBetween is not a native Excel function (in 2003 and
earlier), it is not going to be found under WorksheetFunction. If you
have the ATP VBA reference (you don't need both ATP references, just
the VBA one), you can call RandBetween as if it were a native VBA
function. Note that you must have the add-in loaded and your project
must reference the atpvbaen.xla library.

L = RandBetween(1, 100)

When I use functions from another library, I like to qualify the name
of the function with the library name, just to keep things clear and
well documented.

L = [atpvbaen.xls].RandBetween(1, 100)

The [ ] chars are required because the library name contains a period
(and yes, the referenced library is "xls" not "xla").

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

Hi Bradley,

There is Analysis ToolPak and Analysis ToolPak - VBA. I think you need both.
However, if still won't work then replace the following code

lngRandom = WorksheetFunction _
.RandBetween(1, lngCells)

with these 2 lines of code

Randomize
lngRandom = Int((lngCells * Rnd) + 1)

.