Delete rows based on cell number value lower than certain Value

Hi Good day guys

I am truly stuck with this one Macro I'm trying to create and wonder if anyone could please assist me, would be much appreciated.

I am trying to delete entire rows based on the values in two columns Q & R, I want only the rows remaining that has a cell value 'above' 5. I want this macro to look at column Q & R and if the cells in those columns are below either -5 or +5 then I want the macro to delete those rows.

For example if column Q or R have a cell value of -6;-7;-10 or 6;7;8;10;5 then the rows that those cells are located in will -not- be deleted, but if the values are 4;-4;3;-3;2 or -2 (I am talking about absolute value) the rows containing those cells will be removed and deleted with the cells automatic shifting up so that there are no blank rows between the data I have.

To add condition to this macro making it a bit more complicated, I have another column (O) that has a series of numbers in them that have repetitive duplicate values. The cells that exceed the set value of a absolute 5 , the O column in that row has a number of lets say 120 then all duplicate numbers of 120 in Column O should not be deleted even if the rows do not meet the first condition of exceeding an absolute value of 5.

I am attaching a workbook of mine with added notes and examples of which rows would be deleted with this post, please if you have a moment have a look into it for more clarity.

Useful extra info:
The data I work with will almost never have the same amount of rows, always have the same amount of columns and might not always be in the same named worksheet. The blank Column U is already in use by another macro I have.

Any type of assistance or tips anyone can give me would be helpful, I am open to receive any type of help no matter how small of a help.
Just want to say thanks in advance for having a look at my difficulty I'm having, thanks for your dedicated time. And please don't shy away from asking questions if you feel like helping but do not have enough clarity on what I'm looking for, I'll respond as quick as I can .

Cheers guys.

JBeaucaire, sure no problem, I also tried to make my first sheet a bit more clearer to understand.
The second sheet I created in this example document indicates all the data that would not be deleted.

Hope its easier to understand now

So are you looking for absolute values above five, or above or equal to five? Also, do both Q and R need an absolute above five in order to not be removed, or is one value below absolute five enough to get the row deleted?

Also, about the O-column, do I understand it right when I say that the rows will come in groups with the same value in column O? And if a single Q- or R-cell has a number above absolute five then no rows in the same group should be deleted?

For example, in Sheet1 of your attachment all rows between 17 and 32 have the value 173 in the O-column, but only Q21 has a value above absolute five. Are all of the said rows meant to be kept in this case?

Hey Solaris,

are you looking for absolute values above five, or above or equal to five?

Yes, I am looking for values equal and above a absolute number of 5 , only Q or R need this value , it is not needed for both column cells to contain absolute 5 or above to remain and not be deleted. In simpler terms, if Q contain 5 and R contain 2 then this row will remain.

the rows will come in groups with the same value in column O? And if a single Q- or R-cell has a number above absolute five then no rows in the same group should be deleted?

yes, that is exactly it ^^ So lets say there is a full range of numbers but all part of 'group' 123 in O column , and this entire range of data has only one single number exceeding or equal to absolute 5 in column R or Q then the entire 'group' will remain and not be deleted.

For example, in Sheet1 of your attachment all rows between 17 and 32 have the value 173 in the O-column, but only Q21 has a value above absolute five. Are all of the said rows meant to be kept in this case?

You hit the nail right on the head , that is what I meant to illustrate.

You are from Sweden? ^^ I am actually learning Norwegian currently and going to Sweden in August, my life obsession about Scandinavia :P

Thanks for asking those perfect questions, now someone else reading might also understand better

Ok, then I understand what you want to accomplish =) I take it you have started writing your macro from your first post? In that case, how far have you come, and how are you attacking the problem?

Haha yes I am! Scandinavia is a nice place, but I have to warn you that you'll get by a lot easier with english than with norwegian in Sweden since swedish and norwegian are a bit different ^^

Maybe:

Please Login or Register  to view this content.

Sorry. I misunderstood. I just reviewed your second sample.

How come you have two different groups for each number in column O? You have a group of 172 at the top of the sheet which is to be deleted because no value in Q or R is above +-5. But further down the sheet you have another group of 172s, this time to be kept because there is a value in Q or R above +-5. What is differentiating the two 172 groups?

That would be the TIME values found in the first two columns. Based on the sample data, only adjacent matching rows would be treated as a singel group.

On Sheet 1, the original data, Row 2 and Row 35 are identical, as are many others. You can click "Delete Duplicates" and 27 duplicate rows get deleted. I'm just wondering if that was intentional or not.

Sorry guys I have not had a chance to take a look at all this yet, I will get back to you all later today, promise !

Wow, I apologize for coming back to you all this late, I have been on sea . To come back to you all, JBeaucaire , your code worked like a dream, exactly what I was trying to do. Thank you for being -oh so clever clever :P
dj, yes I copied and pasted some few pieces together to make that example book.

JOHN H. DAVIS , thank you for trying to help nontheless, and thanks everyone else for taking a look at my problem. Much appreciated


Thread Closed ^^