Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows with Too Many of the Same Number
I have seven columns, A-G. All of these columns have different quantities of whole numbers 1-7. I'd like to make a macro to go through row by row and delete any rows that have more than 4 of the same numbers in the same row. How would I go about doing this? -- Losse ------------------------------------------------------------------------ Losse's Profile: http://www.excelforum.com/member.php...o&userid=24813 View this thread: http://www.excelforum.com/showthread...hreadid=559436 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows with Too Many of the Same Number
There's probably an easier way but this should get the job done: Consider haivng an integer variable, one for each of the values fro 1-7 to store the occurance of the particular value in a row. At the end of the row, if any of the variables have a value of 4 o greater then delete the row. of course this should be looped for each row and the variables ar reset at the beginning of a new iteration. Thats how I would do it. hope it helps -- bail ----------------------------------------------------------------------- bails's Profile: http://www.excelforum.com/member.php...fo&userid=3615 View this thread: http://www.excelforum.com/showthread.php?threadid=55943 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows with Too Many of the Same Number
Thanks, I had thought about that, except I need to conserve system resources. This program will be run on a LOT of rows, and I suspect it will crash the computer or at least run extremely slowly. -- Losse ------------------------------------------------------------------------ Losse's Profile: http://www.excelforum.com/member.php...o&userid=24813 View this thread: http://www.excelforum.com/showthread...hreadid=559436 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows with Too Many of the Same Number
How about putting this in H1
=MAX(COUNTIF(A1:g1,A1:g1)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) And drag down. Now you can just filter by that column to show 4 and delete the visible rows. If you need a macro, you could record one when you did it manually. Losse wrote: I have seven columns, A-G. All of these columns have different quantities of whole numbers 1-7. I'd like to make a macro to go through row by row and delete any rows that have more than 4 of the same numbers in the same row. How would I go about doing this? -- Losse ------------------------------------------------------------------------ Losse's Profile: http://www.excelforum.com/member.php...o&userid=24813 View this thread: http://www.excelforum.com/showthread...hreadid=559436 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows with Too Many of the Same Number
Thank you, that should solve my problem -- Loss ----------------------------------------------------------------------- Losse's Profile: http://www.excelforum.com/member.php...fo&userid=2481 View this thread: http://www.excelforum.com/showthread.php?threadid=55943 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting number and alphabet rows and coluns | Excel Discussion (Misc queries) | |||
deleting number and alphabet columns and rows. | Excel Discussion (Misc queries) | |||
Sequential number - adding and deleting rows | Excel Discussion (Misc queries) | |||
Deleting rows with repeated number | Excel Programming | |||
deleting variable number of rows | Excel Programming |