• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to Recognize Subtotal Grouping Range

GregFl

New Member
I have some data on a worksheet grouped by subtotal in two columns with conditional formatting in those two columns. The conditional formatting highlights a cell if it exceeds a certain number. The conditional formatting may highlight a quantity in the data row, the subtotal row, or both. I have VBA code that does all this.

What I need is code that will either identify the subtotal grouping that contains conditional formatting or does not contain conditional formatting. Ultimately I want to keep the entire subtotal grouping associated with the conditional formatted cell (not just the row the conditional formatting is on) and discard the rest. Does anyone know how to do this? Thanks for any suggestions.
 
GregFl
You wrote that You have many features, but
... have You thought - how others would guess - what do You really have, without a sample Excel-file?
That sample file should be as close as Your real file as possible with expected results.
 
GregFl
You wrote that You have many features, but
... have You thought - how others would guess - what do You really have, without a sample Excel-file?
That sample file should be as close as Your real file as possible with expected results.
I am attaching a sample file. I have noted in column T which rows I want to keep or delete. I want to keep or delete the entire subtotal grouping associated with the group by in column B. I hope this helps.
 

Attachments

  • Sample File.xlsx
    30.9 KB · Views: 3
GregFl
I found one cell from N-column, which do not have conditional formatting; N32.
Screenshot 2021-06-16 at 08.50.58.png
What I need is code that will either identify the subtotal grouping that contains conditional formatting or does not contain conditional formatting.
What is Your logic with T-column?
 
GregFl
I found one cell from N-column, which do not have conditional formatting; N32.
View attachment 75005
What I need is code that will either identify the subtotal grouping that contains conditional formatting or does not contain conditional formatting.
What is Your logic with T-column?
N32 is the Grand Total, it will always exceed the quantity so it doesn't matter if it is conditionally formatted or not.
Column T are notes I put to the side to show what I want the macro code to recognize (each subtotal group) and either keep or delete based on whether or not a part of each subtotal group is conditionally formatted or not. Column T will be empty in actual use of the macro.
The actual report will be much larger than this sample (100s of rows more) so I was hoping to have a macro go thru and delete the subtotal groups without having to delete them manually.
 
GregFl
One more time ...
You have asked: ... code that will either identify the subtotal grouping that contains conditional formatting or does not contain conditional formatting. ... Okay?
All those N-column cells are conditional formatted (except that N32) ... Okay?
Column T are notes I put to the side to show what I want the macro code to recognize (each subtotal group) and either keep or delete based on whether or not a part of each subtotal group is conditionally formatted or not.
All those N-column cells are conditional formatted (except that N32) ... Okay?
That means - should all rows then delete? ... except that row (N32) which no matter is it conditional formatted or not?
Did You try to give any answer about T-column - logic?
... except that is not used with in actual use - but now ... it shows Your logic - which You won't explain.
 
Cell N13 is highlighted in yellow because conditional formatting is applied (cell value >31). Because it is highlighted I want to keep Row 13. I also want to keep rows 10-12 because they are all part of the same subtotal grouping based on Column B. Cell O21 is highlighted because of conditional formatting (>42500). I want to keep Rows 19-21 because they are all part of the same subtotal grouping based on Column B. Is there VBA code that will find the highlighted cell, identify all the rows for the associated subtotal grouping, copy and paste those to another sheet? If there is no cell highlighted in a subtotal grouping then I don't want to copy and paste that subtotal grouping. I just want to only see all the subtotal groupings that contain a highlighted cell and nothing else.
 
GregFl
Now, You're writing something different ...
Your sample file has two rules for conditional formatting.
Are You interesting to rows which are true base conditional format as shown in #4 reply ( cell value > 31 and cell value > 30 )?
... and Your refer to some >42500 ... what would be that? There is none rule for that.
Hmm? Now You would like to copy & paste trues to other sheet ... not others ... but later ...
I just want to only see all the subtotal groupings that contain a highlighted cell ... like below?
Screenshot 2021-06-16 at 19.38.49.png
Could You write what do You would like to see?
Please, do not write - what do You won't like to see.
 
Vletm,
I would like to see how you have your screenshot. Also, there is conditional formatting in column O.1623864428911.png
 
GregFl
Okay - there are rules for those too.
But ... when do You answer - what do You want to see ... copy & paste or something else?
Is that #8 reply snapshot something You would like to get?
 
Vletm,
This is what I was trying to do. I have been trying for weeks to come up with a solution but I was stumped. I must admit I don't fully understand all the VBA code (I'm still new to VBA) but I will study it further. I should be able to add this code to my existing VBA and accomplish my task. Thank you so much for your patience and all your help.
 
Back
Top