Thank you so much Jim,
You did it perfectly.
Martyn
"Jim Cone" wrote in message
...
Martyn,
"Now I wonder if we can move the rest of the remaining number list on
column
C1:C35 upwards so that the 'cleared' cells are pushed towards the
bottom of
column C."
Here is my modified code...
'-------------------------------
Sub DisplayRandomNumbers()
Dim RS As Long
Dim objRangeB As Range
Dim objRangeC As Range
Dim blnNotThere As Boolean
Set objRangeB = Range("B1:B35")
Set objRangeC = Range("C1:C35")
' If objRangeC range is blank then fill
' with numbers, clear Columns 1 and 2 and exit.
If WorksheetFunction.CountA(objRangeC) = 0 Then
For RS = 1 To 35
objRangeC(RS).Value = RS
Next 'RS
objRangeB.ClearContents
Range("A1").ClearContents
Exit Sub
End If
' Keep looking until random number is found in objRangeC.
Do While blnNotThere = False
Randomize
RS = Int(Rnd * 35 + 1)
'Find RS position within objRangeC.
If Not IsError(Application.Match(RS, objRangeC, 0)) Then
blnNotThere = True
Range("A1").Value = RS
objRangeB(WorksheetFunction.CountA(objRangeB) + 1).Value = RS
objRangeC(Application.Match(RS, objRangeC, 0)).Delete shift:=xlUp
End If
Loop
Set objRangeB = Nothing
Set objRangeC = Nothing
End Sub
'-----------------------------
Regards,
Jim Cone
San Francisco, CA
"Martyn Wilson" wrote in message
...
Hi, a follow-up addy...:)
Is it possible to fill in the remaining cells as we move along clearing
the
drawn list numbers on column C1:C35 ?
Martyn
- snip -
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 04.07.2004