VBA code to Recognize Subtotal Grouping Range

[GregFl]

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.

 

[vletm]

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]

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.

 

[vletm]

GregFl
I found one cell from N-column, which do not have conditional formatting; N32.

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]

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.

 

[vletm]

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.

 

[GregFl]

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.

 

[vletm]

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?

Could You write what do You would like to see?
Please, do not write - what do You won't like to see.

 

[GregFl]

Vletm,
I would like to see how you have your screenshot. Also, there is conditional formatting in column O.

 

[vletm]

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?

 

[GregFl]

Vletm,
Yes, the #8 reply snapshot is something I would like to get.

 

[vletm]

GregFl
Something like this ... ?
Press [ Do It ] -button to swap those two views.

 

[GregFl]

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.

 

[GregFl]

Hello Vletm,
This macro you helped me with several years ago has been working great. Now I have a need to modify the process. I've added another column (Column T). Similar to before, I want to keep the Subtotal grouping if "ADD" is in column T beside the Total Row (see column B). If the subtotal row is blank in column T, I want to hide the entire subtotal group. I'm attaching a snapshot of the data with a note in column U of how the macro should respond. I tried to modify what you created to meet this need, but I was unsuccessful. I would appreciate any help you could give me once more. Thanks.

 

[vletm]

Where is Your I'm attaching a snapshot of the data?
= I need to see - what is valid output?

 

[GregFl]

Thank you for responding. I thought I included the attachment the first time so let me try this again. Perhaps my firewall is preventing it from going out.

 

[GregFl]

 

[vletm]

I asked I need to see - what is valid output?
Your above snapshot shows ... everything ... hmm?
I continue wondering - what?
If those Your ADDs mean that N & O -column values are under limit then ... that can solve other way.
... if Your ADDs means something else then ... it gotta solve other way.
Here my the latest sample.
[ Do It ] shows everything or 'valid' rows ... without 'subtotal'-formulas

 

[GregFl]

The "ADD" means there is capacity available to add more because Pallets are less than 28 and/or Weight is less than 42500. If Pallets are 28 or more, and/or Weight is 42500 or more, I don't want to have "ADD" in column T and I don't want to keep that subtotal group. I just want the "ADD" subtotal groups left on the sheet.

 

[vletm]

... okay ... hmm?
I guessed ADD-case my way.
... but next You wrote again both ... I don't want and I just want ... why?
Did You notice right to corner ... Pallets 28 ... Weight 42,500?
You can modify those 'limits' as You would like to use.
You can see
# two lists: everything and those which are under limits ... with [ Do It ]-button.
# 'total'.. subtotal rows
# differences to limits with comments
# 'visual' image based limits for both Pallet and Weight as well as those totals
... as below

You can add there as many bookings and loads as needs (in correct places).
You gotta take Yourself that there are needed Load Number Total-row(s).
Press [ Do It ]-button to refresh output.
I should be something that You would need.

 

[GregFl]

I appreciate very much the work you have done but I don't need anything this elaborate. I already have code to identify which loads to "ADD" to so I just need some code that will simply keep the subtotal group if "ADD" is in column T and hide the subtotal group if column T is blank.

 

[vletm]

Your I don't need anything this elaborate ... hmm?
... then I can only and wondering ... what do You need?
Above sample includes Your "ADD" - it even shows, how much could add.
Of course, You can run Your own code before [ Do It ] and
after that my code should still work.
It should do as You wrote:
so I just need some code that will simply keep the subtotal group if "ADD" is in column T and hide the subtotal group if column T is blank.

 

[GregFl]

It finally all made sense and yes your code worked for what I needed. Thank you once again for all your help and patience!

 

[vletm]

Two more variations ...
Sheet1:
> Those comments could take away and show 'need-to-do-to-something' as in this sample.
Sheet2:
> I've still an image that You'll need something else ... hmm?
... You'll try to allocate pallets with 'rules' ( max 28 / 42500 ) or what?
... Here one sample (those steps can hide) to let Excel do it.
... Right side is Your (original) data ( in this sample )
... Press [ Do It ] and You'll get one possible solution to get those pallets 'allocated' with 'rules'
... The results are those green columns ( this can modify to use Your Sheet1-layout ).