Hi Tom,

Thank you very much for the Revised Macro.
It Worked Perfectly Except that it Left the Last Figure of the Sort in
Cell B43. I Resolved it by Changing :-

rng.offset(0,1).Resize(14).ClearContents
to :-
rng.offset(0,1).Resize(15).ClearContents

Thanks Again.
All the Best
Paul



"Tom Ogilvy" wrote in message ...
Why not do it as the first step of this macro:

Sub find14()
With Worksheets("Deletion")
Set Rng = .Range("A1:A42")
rng.offset(0,1).Resize(14).ClearContents
rng.formula = "=row()"
rng.formula = rng.value
End With
With Worksheets("Data")
Set rng1 = .Range(.Range("E14"), _
.Cells(Rows.Count, 10).End(xlUp))
End With

i = rng1.Count
Do While Application.CountA(Rng) 14
Set cell = rng1(i)
Rng(rng1(i).Value).ClearContents
i = i - 1
Loop
With Worksheets("Deletion")
.Range("A1:A42").Copy Destination:=.Range("B2:B43")
.Range("B2:B43").SpecialCells(xlBlanks).Delete Shift:=xlShiftUp
End With
End Sub

or if you want it separate:

Sub ClearData()
Dim rng as Range
With Worksheets("Deletion")
Set Rng = .Range("A1:A42")
rng.offset(0,1).Resize(14).ClearContents
rng.formula = "=row()"
rng.formula = rng.value
End With
End sub

--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
...
Hi Tom,

I Attached your Macro to a Button and it Works Great.
Is there a Small Macro that I can Run After that One that will Re-Set
the 42 Numbers in A1:A42 and Clear the Contents in B2:B43 Please.
I can then Attach it to Another Button Next to the First One.
Thanks for Your Help.

Best Regards
Paul



(Paul Black) wrote in message
m...
Thanks Tom for your Time, it Works Like a Charm. People such as
Yourself make life so Much Easier.
All the Very Best
Paul



"Tom Ogilvy" wrote in message
...
the macro assumed they were already in numerical order in the Sheet
deletion
in A1:A42, so to move them to B2 and delete the spaced

Sub find14()
With Worksheets("Deletion")
Set Rng = .Range("A1:A42")
End With
With Worksheets("Data")
Set rng1 = .Range(.Range("E14"), _
.Cells(Rows.Count, 10).End(xlUp))
End With

i = rng1.Count
Do While Application.CountA(Rng) 14
Set cell = rng1(i)
Rng(rng1(i).Value).ClearContents
i = i - 1
Loop
With Worksheets("Deletion")
.Range("A1:A42").Copy Destination:=.Range("B2:B43")
.Range("B2:B43").SpecialCells(xlBlanks).Delete Shift:=xlShiftUp
End With
End Sub

--
Regards,
Tom Ogilvy



"Paul Black" wrote in message
...
Hi Tom,

Just one last thing. How would you Sort the 14 Numbers after the
Macro
has Run in Numerical Order in the Sheet Named "Deletion" from Cells
B2:B15 please.

Thank You
All the Best
Paul



(Paul Black) wrote in message
m...
Thanks Tom,

I would have Tested it, but the Machine I am at for the Next Few
Days
has not got Excel.
Thanks for All your Help.

All the Best
Paul



"Tom Ogilvy" wrote in message
...
Paul,
I misunderstood your question. The macro would work if there
are
numbers in
A:D

Of course the easiest way to find out would have been to try it.

--
Regards,
Tom Ogilvy


"Paul Black" wrote in message
. ..
Thanks for the Reply Tom,

I am new to VBA and just wanted to get how the Macro worked
clear in
my mind.
I did think that there would be a Problem if there were
Numbers from
1
to 42 in Columns A:D. I do not need the Macro Modifified
however.
Thanks for the Explanations.

All the Best
Paul



"Tom Ogilvy" wrote in message
...
10 is column J. So it finds the bottom of column J.

Would the Macro Still Work if there were Numbers Between 1
and
42 in
Columns A:D.
No, not without modification.

--
Regards,
Tom Ogilvy


"Paul Black" wrote in message
...
Thanks Tom, it Works Perfect.
Out of Interest what does this Bit do
.Cells(Rows.Count, 10)
Would the Macro Still Work if there were Numbers Between 1
and
42 in
Columns A:D.

All the Best
Paul



"Tom Ogilvy" wrote in message
...
Sub find14()
With Worksheets("Deletion")
Set rng = .Range("A1:A42")
End With
With Worksheets("Data")
Set rng1 = .Range(.Range("E14"), _
.Cells(Rows.Count, 10).End(xlUp))
End With

i = rng1.Count
Do While Application.CountA(rng) 14
Set cell = rng1(i)
rng(rng1(i).Value).ClearContents
i = i - 1
Loop

End Sub

--
Regards,
Tom Ogilvy


"Paul Black" wrote in message
. ..
Hi Tom,

Thanks for the Reply.
The 42 Numbers are Listed in a Sheet Called "Deletion"
in
Cells
A1:A42.
The Data is in a Sheet Called "Data" in Cells
E14:J1907, but
the
Data
is Constantly Being Added to.

Thanks in Advance
Paul



(Paul Black) wrote in message
m...
Hi,

I have 6 Columns of Data ( with Numbers from 1 to
42 )
from E to
J.
The Data at the Moment goes from E14:J1907, but More
Data
is
Constantly Being Added. No Number is Higher than 42.
What I would like is for a Macro to Find the Last
Cell in
Column
J
with Data, and Remove that Number from the List 1 to
42.
Then go
One
Cell to the Left and Remove that Number from the
List,
then go
One
Cell to the Left and Remove that Number from the
List etc
upto
and
including Column E. Then I would like it to go back
to
Column J,
But
One Row up and do the Same. There could be the Same
Number
(
i.e.
Number 9 ) in Cells in Several Rows, so the Macro
would
Already
know
this Number has been Removed and move onto the Next.
I would like it to Continue Removing Numbers from
the List
Until
there
are 14 ( Fourteen ) Unique Numbers Left.
I would then like it to List those 14 Numbers
Please.
I hope this makes Sense.

Thanks in Advance
Paul