Excel VBA, Sequential Number JE group, and move large ABS group to new tab

I have a list of "grouped" JE, and I want to sequentially number each "group". Most of the JE are a 2-10 rows, but there are some very large groups (up to 500 sometimes annually). In an Excel 2010 Macro (VBA) how can I number each group, when I have say 5-25 groups (it varies), which consists of anywhere from 2-500 rows (which also varies)?

Secondly, I would like to separate each "group" based on the ABS value amount, and move the groups >20k ABS to a separate worksheet, and also move groups <20k ABS to a separate worksheet.  Again, the number of rows in each group and the number of groups vary.

Attached is a sample worksheet, which includes the numbering desired, as well as the tabs which are the desired end result, where the >20K are on one tab and the <20k are on the next tab.  I am using an Excel Macro to perform to complete these tasks.

Thank you for your help beforehand!
JE-Numbering.xlsx
PRworkerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Place this formula in B2 and filldown
=IF(ISNUMBER(C2),IF(ISNUMBER(C1),B1+1,1),"")

Open in new window

PRworkerAuthor Commented:
Thank you for the formula, it works!  For the macro though, how does the macro know when to stop?  In other words, since there is a blank row between each "group", how can the formula be written so the macro will stop filling downward when there are two blank rows?

Also, do you know how to move the large ABS group to another tab ?

Many thanks in advance for your time and assistance.
aikimarkCommented:
It is a formula.  You use it as needed, manually placing the formula into the cells that matter.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

PRworkerAuthor Commented:
Thank you for your response, however, it will not be manually run, as it will be part of a macro.  The problem is the blank row and filling down in a macro.

Also, can anyone suggest how to move a whole group that has an ABS total that is large to a new tab?

Many thanks in advance for your help.
aikimarkCommented:
Your VBA code will reference a range with a range object that contains all the cells in column B from B2 to the last used row.  Then use the FillDown method for the range object.

I'm looking at a sane method for identifying the blocks of cells.
PRworkerAuthor Commented:
OK, I will use a reference range, and see how it works.  Thank you!

I will await your suggestion for moving the second question to Accept the solution; would that be ok?

Many thanks for all your time and assistance in advance.
PRworkerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for PRworker's comment #a41259346

for the following reason:

I will accept the formula as one solution and post the second question as a separate question.  Thank you for your time and assistance.
aikimarkCommented:
Please accept my comment (http:#a41250844 ) as the solution, since it is the comment with the formula.

I have been unable to use the directprecedents() collection.  It can be done with VBA, but I was hoping for a formula-only solution.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PRworkerAuthor Commented:
HI Aikimark,

My apologies, I thought I had accepted the solution, and when I had not heard a response on the second part, I thought it may be best to post it as a separate question.

For the second question, I would prefer VBA as I am using a macro.  Many thanks again for all your time and assistance!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.