We help IT Professionals succeed at work.

Remove rows containing 2 or more numbers from different decis

Medium Priority
49 Views
Last Modified: 2020-02-20
The idea is to create a script that removes rows where there are 2 or more numbers from different "decis". A deci is defined as numbers from 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79, 80-89, and 90-99,

There should be:

1) a prompt would ask for input of "Maximum numbers allowed from different decis" or something similar. If the input is one (1) then
2) choose the sheet to be processed
3) sheet "Numbers_3" would be edited as follows:

 This process should be able to be repeated for any sheet similar to "Numbers_3"

Remove rows containing 2 or more numbers from different decis
Notice how all the green rows contain number from only 1 "deci". That is the goal.
Remove-rows-with-2-or-more-numbers-.xlsb
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are you saying that 3-4-10-11-20 which was valid in the last question is not valid now because there is more than one decie with 2 or more entries? And are you also saing that this should happen only if the "Maximum numbers allowed from different decis" choice is 1?

Author

Commented:

Are you saying that 3-4-10-11-20 which was valid in the last question is not valid now because there is more than one decie with 2 or more entries?

correct.


And are you also saing that this should happen only if the "Maximum numbers allowed from different decis" choice is 1?


not sure How to answer this. But since 3-4 and 10-11 are 2 numbers from different “decis” they should be removed. it’s similar to the last script except this time it’s numbers from different decis.

Author

Commented:

BTW. You can use the last script and just add this to it if it’s easier.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What should happen if you enter 3 or more as "Maximum numbers allowed from different decis", since in a 5-cell set there can't be more than one deci with 3 or more?

Should I not even ask the "Maximum numbers allowed from different decis" question and assume it's always 2?

Author

Commented:

The first is not possible. 


The second is correct. No need to ask the question since there can only be a maximum of 2 from same ”deci”. 

Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
In the attached workbook Numbers_3 is done. (4 1/2 minutes).
29173375.xlsb

Author

Commented:

Is it possible to merge this and the last script so they’ll run one after the other? Or will that be too much overhead?

Author

Commented:

Martin,


I see more than one macro (See picture below). Which one Am I running for this post?


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The one that's highlighted. Notice that you have "All Open Workbooks" selected and that the two macros that start with 'Mega are not in my workbook.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
It should be fairly easy to do.

Author

Commented:

ran it on sheet 7 and it ran without error. But on sheet 3 I got the error below. It seems since you ran it on sheet 3 it won't run again on the same sheet.


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This does not do the combined search yet that you asked for. Instead it's a version of my previous workbook that runs in about 8 seconds!

Again Numbers_3 is done but now you won't have a problem doing it again.
29173375a.xlsb

Author

Commented:

No worries. I’d rather get that error. I got it on the previous one also. It alerts me that the sheet was already processed.

Author

Commented:

The request for combined version has been posted. I have reset 3 and 7 and added 4 and 8 for testing purposes, if needed.

Rowan ScottEngineering
CERTIFIED EXPERT

Commented:
Hi
I had a go at this just for fun. This runs the deletion in about 1 and a half minutes by first sorting so that all the deletion can be done in one hit.
It sorts back to the original order afterwards.

It assumes columns F and G are always blank and uses them for sorting.

Run the sub called main to test it out.

This may or may not be helpful. I haven't looked at the other solutions provided but posting it here just it case anyone wants it.
Remove-rows---via-sort-method.xlsb