Random Select
 

Hi, Ive got 3 sheets of data, doesnt completly fill each sheet but
almost. Id like to randomly select a block of cells in a columm. eg
a1:a100, a100:a200 etc. across the 3 sheets. Im guessing id randomly
select the sheet then select the cells. Then Id like to loop through
the selected cells. Any ideas please.
Regards Robert

Random Select
 

Sub pick_a_bunch()
'
'pick a randon sheet
'
n = Evaluate("Randbetween(1,3)")
Sheets(n).Activate
'
'pick a random column
'

cols = "randbetween(1," & Columns.Count & ")"
col = Evaluate(s)
'
'pick random row range
'
s = "randbetween(1," & Rows.Count & ")"
rStart = Evaluate(s)
s2 = "randbetween(" & rStart & "," & Rows.Count & ")"
rEnd = Evaluate(s2)

For i = rStart To rEnd
MsgBox (Cells(i, cols).Address)
Next

End Sub


--
Gary''s Student - gsnu2007


"RobcPettit" wrote:

Hi, Ive got 3 sheets of data, doesnt completly fill each sheet but
almost. Id like to randomly select a block of cells in a columm. eg
a1:a100, a100:a200 etc. across the 3 sheets. Im guessing id randomly
select the sheet then select the cells. Then Id like to loop through
the selected cells. Any ideas please.
Regards Robert

Random Select
 

Try this

Sub rancells()

Const MaxRow = 1000
Dim RandRange(3)

For shtcount = 1 To 3
Num1 = (MaxRow * Rnd()) + 1
Num2 = (MaxRow * Rnd()) + 1
If mum1 < Num2 Then
FirstRow = Int(Num1)
LastRow = Int(Num2)
Else
FirstRow = Int(Num2)
LastRow = Int(Num1)
End If
With Sheets(shtcount)
Set RandRange(shtcount - 1) = _
.Range("A" & FirstRow & ":A" & _
LastRow)
End With
Next shtcount

For shtcount = 1 To 3
For Each cell In RandRange(shtcount - 1)
'enter your code here
Next cell
Next shtcount
End Sub

"RobcPettit" wrote:

Hi, Ive got 3 sheets of data, doesnt completly fill each sheet but
almost. Id like to randomly select a block of cells in a columm. eg
a1:a100, a100:a200 etc. across the 3 sheets. Im guessing id randomly
select the sheet then select the cells. Then Id like to loop through
the selected cells. Any ideas please.
Regards Robert

Random Select
 

Thankyou both for your help. Done the trick. Sorry for delay in reply
been away. Much appreciated.
Regards Robert