Random Select

[RobcPettit]

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

 

[Guest]

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

 

[Guest]

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]

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