Hide Rows based on "A"'s Criteria

What am I doing wrong here?

I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.

Please Login or Register  to view this content.

When you are evaluating the same colum over and over again for the same value, looping isn't necessary. Just use the AutoFilter, filter the data by that column and that value, then do your "delete" on everything left visible.

Please Login or Register  to view this content.

The addition of the row 1 appeared to be necessary because your original macro was evaluating all the way up to row1. If row 1 already has "titles", then you can remove the Insert and Delete code at the top and bottom.

If you have trouble editing this to your sheet, post it up and we'll help.

My apologies for using the word "delete" instead of "hide". Everything else stated is still the applicable solution.

I just tried your code, and it errored out when adding the autofilters. The sheet has to start on A5, I changed that and it still errored out

I understand where your going with this code, but I really just want it to look at each individual row, and delete all rows with "Filler" in the "A" column - This sheet is a lot bigger and this is just one section of the code. Then go and hide any unneccessary or "empty" subtotal total rows.

I'm going to attach the sheet, but just so theres no confusion please note:

The sheet baisically makes a "fake" subtotal scenerio for everything that could possibly be put on the sheet originally to subtotal. The reason for this is because the way it sorts it, it can't account for a specific criteria and it will lump it together otherwise. If I make the "fake" subtotal it divides it out perfectly. As of now it works FINE, I just need to to erase all the empty or unused subtotals and all the "filler" rows.

Thanks for the quick response BTW

I don't see any filler rows on your sample. Can you expand to a large sampling that actually includes the info needing to be hidden?

Also, I know we're trying to solve one issue, but based on your last post, if you showed an example of the MATH you're trying to get right by adding these filler rows, perhaps we can solve that instead making this whole filler thing just go away.

Click on the "Subtotal" button - they will appear in white text on white background

Math involved?? There shouldn't be.... Filler makes it a place holder so it will subtotal correctly.

You can use AutoFilter to do most of the work you're doing in your CommandButton2 macro and significantly increase the speed by not looping through the whole dataset over and over again.

For instance, this is how I would write section #9, it leaves the FILLER rows hidden after painting them white...is this what you had in mind?:

Please Login or Register  to view this content.

Section 8 really only needs to find the one Grand Total cell, right? NO need to loop the entire sheet for that, just go to it directly:

Please Login or Register  to view this content.

You tend to manually select things before applying your actions. That's only required if you're human, VBA can act on cells/ranges directly without selecting them first...at all. Sections 5 & 6 demonstrate this:

Please Login or Register  to view this content.

I would rewrite the whole FILLER macro without all the selecting and FillDown stuff:

Please Login or Register  to view this content.

Try using the Autofilter on the Totals column then to show only values greater than zero.

We are a village. Glad to help.