VBA That Will Group Specific Blank Rows

[Steve Belsch]

Hi VBA Experts,

I am looking for some ideas on how to accomplish the following.

I have attached two files for reference. One file has the before I run the VBA look and the second has the file post running the Macro.

If you look at the Post Running the macro file it will show you that I have added subtotals for each unique PO# and then I have added 5 blank rows. These blank rows are intentionally added for the Project Manager to populate with information and numbers if needed. But if you look past the last subtotal all the way to the first text where it says "CRO_1" I want to group those blank rows (not the 5 blank rows in each subtotal. And I would like to do this on each tab (for this example document I have reduced it to 3 tabs of data to reduce the size of the document. In the file I am working in it is over 250 tabs). And each tab will be different. It may be 50 blank rows or it may be 105 blank rows.

Any thoughts on how to accomplish this? Maybe a short DoWhile loop or something that just skips to the last "Subtotal" text and then finds all blank rows (based on column A) and groups them?

Thanks for any help and ideas.

Steve

[Leith Ross]

Hello Steve,

Happy New Year!

I would like to help but I am unable to. My computer has eight processors and 8 Gb of RAM. When either workbook opens, my system can not calculate the formulas. All 8 CPUs are running at 100% and 0% resources available. I am running Windows 7 32/64 bit with Office 2010. How is your computer setup?

[Steve Belsch]

Leith,

Thanks for the reply. I am not sure my specs. I will check and reply again. But if I put it on Manual Calc it works. It does take a while, maybe 4 minutes.

Thanks,
Steve

[Steve Belsch]

Leith,

Thanks for the reply. My computer has 4 logical processors and a RAM of 16 GB. Windows 10, 64 bit, 2.81 GHz. I am running at about 11% utilization.

Can you maybe put manual calc on and see if you can check the VBA code and come up with some suggestions?

Thanks for the help.

Happy New Year!
Steve

[snb]

Why don't you mention the worksheet you are talking about ?

Before posting a workbookk you should remove all formulae containing complete columns: like $J$2:$J1048576.
In fact you should never use those. (lesson 1 on formulae).

My system was able to open the files (XP, Excel 2010, 4Gb RAM, 3 processors).
But before continuing: rethink the project.
Use 1 worksheet as a flat database.
Abstain from every gadgeting/formatting/grouping in Excel.
If you post a file restrict it to the the bare necessity of illustrating yoiur question; think from the perspective of a potential helper.

I asked a moderator to remove your files, since they cause harm to potential helpers..

[Steve Belsch]

SNB,

Thank you for the tips. Please reference tab named 05.301 in the "VBA Code to Group Specific Blank Rows - Post Macro Run" file. This file is only 0.7 MB. I have made it values (no formulas) but, it will still contain the VBA code so you can take a look to see if you could think of a solution to my issue. Does that help?

A few questions.
- Why abstain from formatting using VBA code? My workbook will be over 250 tabs and I do not want to format each one individually.
- Why should I not use a formula containing complete columns?

Thanks again for any help.

Regards,
Steve

[jolivanes]

Re: "Why should I not use a formula containing complete columns?"
Example?
Have an empty workbook and save it. See what it's size is.
You said you have 250 sheets but for this just use only 100.
Run this code to just put a formula in one column only.

Sub AAAAA()
Dim j As Long
For j = 1 To 100
Sheets(j).Range(Sheets(j).Cells(1, 1), Sheets(j).Cells(1000000, 1)).Formula = "=Now()"
Next j
End Sub

When finished, if it does, save it again and see what the size is.

[Steve Belsch]

jolivanes,

Thank you for the code. I will try it out and get back to you on this thread with the result.

Also, would you have any thoughts on my original issue at the beginning of this thread? I am still stuck on that one.

Thank you.
Steve

[Steve Belsch]

jolivanes,

Thank you for the code. I will try it out and get back to you on this thread with the result.

Also, would you have any thoughts on my original issue at the beginning of this thread? I am still stuck on that one.

Thank you.
Steve