| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Reducing Numbers Please
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
Hi Tom,
Sorry to Trouble you Again. I have Changed the Structure of the Sheet "Deletion". The Modified Macro Below Works Fine :- Sub Find_13_Numbers() With Worksheets("Deletion") Set rng = .Range("B2:B28") End With With Worksheets("Results") Set rng1 = .Range(.Range("K6"), _ .Cells(Rows.Count, 5).End(xlUp)) End With i = rng1.Count Do While Application.CountA(rng) 13 Set cell = rng1(i) rng(rng1(i).Value).ClearContents i = i - 1 Loop With Worksheets("Deletion") .Range("B2:B28").Copy Destination:=.Range("D2:D28") .Range("D2:D28").SpecialCells(xlBlanks).Delete Shift:=xlShiftUp End With End Sub I have Tried to NO Avail to Modify the Macro Below :- Sub Reset() Dim rng As Range With Worksheets("Deletion") Set rng = .Range("B2:B28") rng.Offset(0, 2).Resize(13).ClearContents rng.Formula = "=row()" rng.Formula = rng.Value End With End Sub It Deletes the 13 Numbers in "D2:D14" O.K. It Does Reset the 27 Numbers in "B2:B28", BUT they go from Number 2 to Number 28, Instead of Number 1 to Number 27. What am I Missing. Thank You Very Much Tom. All the Best Paul (Paul Black) wrote in message m... 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 |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
Sub Reset()
Dim rng As Range With Worksheets("Deletion") Set rng = .Range("B2:B28") rng.Offset(0, 2).Resize(13).ClearContents rng.Formula = "=row()-1" rng.Formula = rng.Value End With End Sub -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Hi Tom, Sorry to Trouble you Again. I have Changed the Structure of the Sheet "Deletion". The Modified Macro Below Works Fine :- Sub Find_13_Numbers() With Worksheets("Deletion") Set rng = .Range("B2:B28") End With With Worksheets("Results") Set rng1 = .Range(.Range("K6"), _ .Cells(Rows.Count, 5).End(xlUp)) End With i = rng1.Count Do While Application.CountA(rng) 13 Set cell = rng1(i) rng(rng1(i).Value).ClearContents i = i - 1 Loop With Worksheets("Deletion") .Range("B2:B28").Copy Destination:=.Range("D2:D28") .Range("D2:D28").SpecialCells(xlBlanks).Delete Shift:=xlShiftUp End With End Sub I have Tried to NO Avail to Modify the Macro Below :- Sub Reset() Dim rng As Range With Worksheets("Deletion") Set rng = .Range("B2:B28") rng.Offset(0, 2).Resize(13).ClearContents rng.Formula = "=row()" rng.Formula = rng.Value End With End Sub It Deletes the 13 Numbers in "D2:D14" O.K. It Does Reset the 27 Numbers in "B2:B28", BUT they go from Number 2 to Number 28, Instead of Number 1 to Number 27. What am I Missing. Thank You Very Much Tom. All the Best Paul (Paul Black) wrote in message m... 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 |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Reducing Numbers Please
Thanks Tom, it Works Great.
All the Best Paul "Tom Ogilvy" wrote in message ... Sub Reset() Dim rng As Range With Worksheets("Deletion") Set rng = .Range("B2:B28") rng.Offset(0, 2).Resize(13).ClearContents rng.Formula = "=row()-1" rng.Formula = rng.Value End With End Sub -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Hi Tom, Sorry to Trouble you Again. I have Changed the Structure of the Sheet "Deletion". The Modified Macro Below Works Fine :- Sub Find_13_Numbers() With Worksheets("Deletion") Set rng = .Range("B2:B28") End With With Worksheets("Results") Set rng1 = .Range(.Range("K6"), _ .Cells(Rows.Count, 5).End(xlUp)) End With i = rng1.Count Do While Application.CountA(rng) 13 Set cell = rng1(i) rng(rng1(i).Value).ClearContents i = i - 1 Loop With Worksheets("Deletion") .Range("B2:B28").Copy Destination:=.Range("D2:D28") .Range("D2:D28").SpecialCells(xlBlanks).Delete Shift:=xlShiftUp End With End Sub I have Tried to NO Avail to Modify the Macro Below :- Sub Reset() Dim rng As Range With Worksheets("Deletion") Set rng = .Range("B2:B28") rng.Offset(0, 2).Resize(13).ClearContents rng.Formula = "=row()" rng.Formula = rng.Value End With End Sub It Deletes the 13 Numbers in "D2:D14" O.K. It Does Reset the 27 Numbers in "B2:B28", BUT they go from Number 2 to Number 28, Instead of Number 1 to Number 27. What am I Missing. Thank You Very Much Tom. All the Best Paul (Paul Black) wrote in message m... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reducing numbers by one in defined areas over several sheets | Excel Worksheet Functions | |||
| Reducing Spreadsheet | Excel Discussion (Misc queries) | |||
| Reducing A Number By 1 (one) | Excel Programming | |||
| Reducing Code | Excel Programming | |||
| Reducing Code | Excel Programming | |||
Linear Mode
