Need Subtotal in attached code to NOTcount blanks or non visible cells

Please help me find why the SUBTOTAL (in the called subroutine, "InsertTotals") is counting non blanks and/or non visible cells. (Columns C, D and E that are being subtotaled, or 'counted' have an "X" in the cells. New subtotals occur on changes in Col. A.) Any substantive help will be duly acknowledged. Thanks!

Please Login or Register  to view this content.

In this line :

Please Login or Register  to view this content.

Would changing it to this work :

Please Login or Register  to view this content.

I made the change and get Run-time error '1004' "Application-defined or object-defined error. Debug just stops at that line now.

I step through the code and it just stops at that line. Is it a clue that the little spinning circle spins for nearly a minute? This code is being applied to a filtered table if that's not already apparent.

Just went back into code and want to ask if the "SkipBlanks:=False" is applying to my columns being counted or not really pertinent to my problem?

Rows(iRow + 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Holy crap, I'm so close I could taste it!! Changing "Setting SkipBlanks:False" to "True" actually worked BUT only for the column E subtotals. Columns C and D are still counting all rows in that subtotal rather than any "X"s. Could I have some hidden or remnant formatting or formulas tuck somewhere in the darkness in the cells in those two columns?

There may be a remnant residing within.

You could try highlighting those cell and selecting CLEAR CONTENT to see what happens.

After reading the Microsoft knowledge base, my vbInteger was a guess.

Logit, your "guess" was a very sound one. I had no clue where to start debugging until you brought attention to that line of code. Plus, you are correct in pointing out where my last hurdle may reside. The attached code is what I apply to a source worksheet to create the subject worksheet for subtotaling.

Right after the DIM statement, I do attempt to clear contents but I suspect I should be clearing the contents and format; however, I don't want to disturb the first 3 rows that are holding my filters and keeping column width formatting in place.

Would ".ClearFormats" be workable, here? I would try it off-hand but I've been getting into loops and destroying other worksheets and formulas by "shot-gunning" this project! : / Plus, I don't know how to explain column E giving the correct subtotal. Hints??

Please Login or Register  to view this content.

Don't experiment with your only file !!

Make a copy and destroy that one.

Then make another and repeat.

Saves alot of crying late at night.

I hear ya! Just counted 40! iterations of the workbook on my desktop as we speak - from the last 10 days, alone. (There are as many in separate folder from earlier attempts - each worksheet marking a success of some sort.)

This last issue has me stumped over the last 6 days (sadly, but I'll admit it). Thanks for taking time to look at this. Hopefully, I will figure out what the problem is ... other than the root problem being I'm a total noob to Excel VBA, that is!

If this is just a one time thing, why not just highlight the rows then select Clear Contents ?

Nah, if this were one time I'd have been done two months ago when I tackled this project.
It didn't start out as a coding project but "scope creep" sure made it that way! Way back
in the day, I did some ad hoc report development in relational databases (Like RAMIS and FOCUS)
because no one else could or wanted to do it. And, I did an entire database with bells and
whistles in ACCESS...then immediately forgot everything cuz that wasn't my job, really.

I love the logic in coding. I first taught myself BASIC on my Commodore 64 (which I still have).
Heck, I even partitioned the "drive" on my C64! And, I did a very, very small amount of psuedo-
machine coding because it was interesting. But, time passed and coding moved into its "object-oriented"
manifestation and I'm finding myself in over my head. The (re-)learning curve has been nearly straight up!
In fact, less than 6o days ago I had to ask how to enter a simple SUM in a cell in EXCEL. REALLY!!

Now, I have too much time into it to quit!! The perils of being stubborn, I guess!.

Can't seem to get to my goal with either. Is there a way to share a workbook/spreadsheet without it being open to the world?
I'm at a loss for how to describe in writing what I'm seeing. I generated two workbooks. One unfiltered and another with a simple filter to show that hidden lines and some blank entries in columns C, D and E are present.

I just realized the irony of asking the public for help without showing what I need help with!! But, I can't figure how to attach or link the two workbooks I created with some generic, made-up contents (one for saving as a reference-restart and another with a simple filter applied). Both contain the VBA for subtotaling that is getting the best of me. So, some guidance on how to attach or link to these workbooks is appreciated!

Well .. firstly there is a size limit. I believe its 1000 kb.

To attach a file, click on GO ADVANCED (just to the bottom right from where I'm typing now. You'll need to look on your side at the Reply To Thread window).

That takes you to another page, about 1/2 way down, there is a link MANAGE ATTACHMENTS. Click on it and follow the bouncing ball ! (not literally).

You can always ZIP your files to reduce size or even place them on a CLOUD site like DROP BOX or such.

You'll get more responses from others if you use the FORUM's method of attachment. Lots of folks don't like to download stuff from third parties.

Pictures of what you see are alot of help, usually. Should always be an actual file.

Hopefully, I re-attached the correct file. It should contain the macro to Subtotal on "Program". Note: the red highlighted "questions" are of high importance and I will be using the hard-coded "TRUE" in the column to the right to make a separate flag if that question has a "X" in column E (but that's a later task for me).

Ok, got the file. Reviewed it.

I'm having a difficult time understanding the results.

I've looked at column H and see TRUE written in a number of the cells. Some correspond
to Cols C,D,E having an X in the NO column and then there are a few that don't correspond.

Is the goal to total all of the TRUES / or all of the rows where the NO column has an X / or ???

My mind is already spinning.

(Please pardon my ignorance here)

Sorry for the confusion. The goal is to subtotal (on the same inserted row) the "X"s that occur in columns C, D and E, typically when filtered by column "A" OR "B".

Not necessarily part of my problem at hand, are some very significant questions that are (should be) highlighted in red text. These questions are so few and far between that I can hard-code "TRUE" into an adjacent helper column. I will later do some output to another worksheet with those questions in column G that have an "X" in column E AND have "TRUE" in the helper column.

BTW, the ignorance is on my part...I'm the noob, here!

Logit,, I think you're onto something! I was concentrating on inserting subtotals but this works too well to ignore. Plus, it will give me a more stable range for which to send these numbers to another worksheet and maybe even do that conditional selection(?) for TRUE (column H) questions that also have an X in column E.

The only thing I can't figure out is why column E loses its red fill color. This minor detail is what the recipient seems to be particularly proud of, since the color red clearly says "WARNING!"

The spreadsheet can be 25 rows long or 1,000 rows. I had to skip out to help family and will play with it this evening. Thanks!!!!!!

There were TWO conditional formatting rules for Column E.

The first was for ORANGE, the second was for RED.

Since they both 'fired' under the same circumstance, EXCEL always goes with first rule. No difference between the rules,
so it chose ORANGE.

I've deleted that rule in the attachment.

Logit, I'm calling this one SOLVED! Thanks so much for your efforts ... and patience! (When I figure out how to mark it that way, anyway! Have a good evening!)

Great !

To mark this thread solved, go to the upper right corner of the page - look for "Thread Tools" in small letters. Click the down arrow and select "Mark Thread As Solved".

I see that at the moment, with the change to the new server, that function is not there. But, please check back to see when it is , then finish it up.

Glad to help.