# 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
###### Who is Participating?

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.

Commented:
Place this formula in B2 and filldown
=IF(ISNUMBER(C2),IF(ISNUMBER(C1),B1+1,1),"")
Author 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 ?

Commented:
It is a formula.  You use it as needed, manually placing the formula into the cells that matter.
Author 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?

Commented:
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.
Author 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?

Author 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.
Commented:
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