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

Where is the list of 42 numbers located.

--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
...
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

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

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

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

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

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