Macro to extend row grouping to include blank row

I had thought that we were able to upload spreadsheets here, but I can't figure out how to do it. There's an icon at the top of the topic options with a paperclip, but clicking that only pops up a small, empty, rectangular box underneath it. I can try to explain this without the spreadsheet, if necessary, but think it would be easier with the actual spreadsheet. If I'm not seeing how to upload a file, please let me know how to do it.

I've got a spreadsheet where I would like to use a macro to add a blank row to an existing group of rows, until it encounters two blank rows. At that point, I would like the macro to stop.

Basically, in the screenshots below ("before" on the left, "after" on the right), I would like the macro to start looking in cell B5, then continue down, looking for a blank cell in column B. If there are not two blank cells/rows together, I would like to extend the grouping down to include the single blank row. When it encounters two blank cells/rows, I would like the macro to do the grouping one more time, and then stop. So, in the screenshot, when it gets to cell B33, it will add row 33 to the group above, and then it will stop since cell B34 is also blank.

Grouping.jpg Grouping (after).jpg

Thanks for your help. You guys are great!!!

Here are the new instructions:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

*this will find your last row:

Please Login or Register  to view this content.

Thanks for the rep!

Is this what you want?

Please Login or Register  to view this content.

Your attachment is invalid.

Directions for running the routine(s) just supplied

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

That's what grouping does - did you want all the data grouped or just the first two columns.
Is grouping what you want at all?

Have you clicked on the 1,2 both vertical and horizontal? And, is it what you want??

Well then maybe:

Please Login or Register  to view this content.

I feel like a complete moron because I can't completely understand what your code is supposed to be doing, so I can't even attempt trying to fix anything without more of your help.

This has the basic idea...it groups a blank line at the very end. But it should be adding the blank line after each group to that same group.

Right now, it's doing this...

New Result.jpg


When it should be doing this...

Desired Result.jpg


Notice that row 16 is now part of the PAC 127501 group, and row 20 is now part of the PAC 127901 group.

The last group farther down the spreadsheet would now look like this with row 53 now being part of the PAC 3997801380 group, where the macro would now end.

Last Group.jpg


These are my grouping options in the "Outline" settings:

Grouping Options.jpg

I truly appreciate all the time you're spending on this. I just wish I knew alot more about this so I didn't have to come across as being so lame.

I'll try again tomorrow - and nobody's perfect!

Well I've tried and had no luck - I'm submitting this problem to the Forum.

Hi Styler001
Try this Code in the attached...

Please Login or Register  to view this content.

Groups have to be separated by an empty row.

Hi Styler001

Change the indicated Lines of Code...

Please Login or Register  to view this content.

You're welcome...glad I could help. Thanks for the Rep.

I've been playing with this ever since John posted the solution and I've found that this combination works rather handily. You needn't run Styler separately as I've called in within John's code:

Please Login or Register  to view this content.

You're welcome hope I'm not fixing anything that's not broke