Summing Blank Rows in a file

[BryceVBA]

Hello all,

I'm trying to sum most of the blank rows in the attached file from column H to column T. I've used the following code to complete the task with a different file. With this code I only had to sum two tables. With the attached file I need to sum like 50 tables. So I wanted to see if anyone could help me with some code that would do this more efficiently than me creating 100's of last row variables to find the blank rows.

Please Login or Register  to view this content.

EDIT: UPDATED ATTACHMENT XLSM AND VBA CODE TO CURRENT SUGGUSTION

[k64]

Try this:

Please Login or Register  to view this content.

[BryceVBA]

Try this:

Please Login or Register  to view this content.

This code sort of works. It definitely finds the blank rows and fills them with a formula, but the formula is wrong in a sense that it sums all previous tables into the current tables sum formula. Also, sums all the blank rows in between the big tables (The highlighted cells "These rows do not get summed').

[jaslake]

Hi Bryce

Try this Code...

Please Login or Register  to view this content.

How does one know where these Rows are going to be...any way to know that? Does the Worksheet actually say "These rows do not get summed"?

the blank rows in between the big tables (The highlighted cells "These rows do not get summed').

[k64]

My bad, I didn't think about the fact that previous blank rows wouldn't be blank anymore. Simple fix:

Please Login or Register  to view this content.

[BryceVBA]

How does one know where these Rows are going to be...any way to know that? Does the Worksheet actually say "These rows do not get summed"?

The actual wb doesn't say "These rows do not get summed", I just edited that in.

On the gap in between the large tables, there is no absolute row value in which the gap appears. As the year goes on, more rows will be added to both tables, pushing the gap down.

The script works pretty good though. The only set back was that summed the rows in the gap between the large tables. Also bold the formulas, but that should be a quick fix.

[BryceVBA]

My bad, I didn't think about the fact that previous blank rows wouldn't be blank anymore. Simple fix:

End Sub[/CODE]

This code didn't seem to do anything. VBA didn't error out. not sure what happened. I'll take a closer look at it tomorrow when I'm back at work. The file is attached if you want to try running it.

[jaslake]

Hi Bryce

Yes, quick fix...

Also bold the formulas, but that should be a quick fix.

You mention "both tables". How is this single Worksheet created from those "both tables"? What's the process...perhaps we can deal with these outliers up front. What's your "RAW Data" look like...or am I looking at it.

[k64]

That's what I get for rushing. Hopefully third time is a charm:

Please Login or Register  to view this content.

[jaslake]

Hi Bryce

This Code in the attached appears to deal with these...let me know...

The only set back was that summed the rows in the gap between the large tables. Also bold the formulas, but that should be a quick fix.

Please Login or Register  to view this content.

[BryceVBA]

Hi Bryce

Yes, quick fix...


You mention "both tables". How is this single Worksheet created from those "both tables"? What's the process...perhaps we can deal with these outliers up front. What's your "RAW Data" look like...or am I looking at it.

I added this code to bold the sum rows

Please Login or Register  to view this content.

The report itself is just raw data exported straight from SAP.

Still not sure how to check if the blank rows need filled in with the formula or not. Possibly a countif function?

Let me know if you can think of anything.

[BryceVBA]

Hi Bryce

This Code in the attached appears to deal with these...let me know...

Interesting way of dealing with the blank rows in between tables, but can't argue with the results. It works perfect. Awesome job!

[jaslake]

Hi Bryce...try my latest...let me know.

[jaslake]

Hi Bryce

It's what I had in my toolbox...

Interesting way of dealing with the blank rows in between tables

Glad it works for you...thanks for the Rep.