How to select random names

wizardmagu

New Member
Joined
Dec 27, 2012
Messages
30
copied a randomize VBA to randomly select a name in a given range and highlight it green. I then recorded a step to bring the highlighted one to the top. The issue I have is that my # of rows varies and I need it to stop looking dependent on the last row.

I have a button that I assigned this macro to and when I push it it will randomize the list, highlight a name in green and also sort it to the top. Any thoughts on controlling the range?

Sub Pick_Name()
Randomize
Range("A1:A1000").Interior.ColorIndex = xlNone
Cells(Int((80 * Rnd) + 1), 1).Interior.Color = vbGreen
ActiveWorkbook.Worksheets("Sheet1 (2)").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1 (2)").AutoFilter.Sort.SortFields.Add(Range( _
"A1"), xlSortOnCellColor, xlAscending, , xlSortTextAsNumbers).SortOnValue. _
Color = RGB(0, 255, 0)
With ActiveWorkbook.Worksheets("Sheet1 (2)").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,674
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Pick_Name()

Randomize


With ActiveSheet
   With .Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Interior.ColorIndex = xlNone

      .Cells(Int((.Rows.Count * Rnd) + 1), 1).Interior.Color = vbGreen
   End With
   .AutoFilter.Sort.SortFields.Clear
   .AutoFilter.Sort.SortFields.Add(Range("A1"), xlSortOnCellColor, xlAscending, , xlSortTextAsNumbers).SortOnValue.Color = RGB(0, 255, 0)

    With .AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub
 

wizardmagu

New Member
Joined
Dec 27, 2012
Messages
30
That worked... Thank you very much. Another question I have is can I add a cell in my excel file "H5" and have the VBA code randomize and highlight that number of cells? For example, if I put in the number 7 into H5 and when I push the button 7 cells are highlighted?

Maybe thats too much but thought I would ask
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,674
Office Version
  1. 365
Platform
  1. Windows
That would need a new thread, as it's a totally different question.
 

wizardmagu

New Member
Joined
Dec 27, 2012
Messages
30

ADVERTISEMENT

ok gotcha. On that VBA code you supplied if I paste in new information and hit the button to randomize I get an error message. I think its because I would have to manually add a filter. Can I get away from that with a modification to the code?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,674
Office Version
  1. 365
Platform
  1. Windows
Yup, just add the line in blue as shown
Rich (BB code):
   End With
   If Not .AutoFilterMode Then .Range("A1").AutoFilter
   .AutoFilter.Sort.SortFields.Clear
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,674
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,838
Messages
5,627,181
Members
416,227
Latest member
Abid Fattani

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top