Add to Autonumbering macro!

**Spellbound** · 10-01-2008, 12:33 PM

Hi all

I have found this code which inserts a sequential number in Column A skipping any blank rows.

Please Login or Register  to view this content.

This does the job fine but I need to prefix the number with the letter S.

The other problem that I have with this code is that I have to adjust the row numbers manually because after the last applicable row, there is some summary information which should not be included with the numbering.

The summary information is seperated by a blank row, so the code just carries on unless I fix the number of rows as above. However this will change each week, some rows may be deleted and more often than not the number of rows will increase.

The only way that I can think of resolving this is to modify the code so that it stops at 2 rows above a constant piece of text in the summary. In this instance G363 contains the text:

CUSTOMER TOTAL =

but I have no idea of how to incorporate either of these features within this piece of code.

I originally tried using formulas to do the auto-numbering but this was very messy trying to work round the blank rows, which are not at constant intervals.

TIA ...spellbound

**Leith Ross** · 10-01-2008, 04:11 PM

Hello Spellbound,

This macro is based on the one you posted. It checks column "B" starting at row 5 and continues down to the last entry in column "B". The macro automatically determines the end of the dynamic range for you so you don't have to adjust the rows in the code. If the contents of cell "B" are empty then the cell in column "A" doesn't get a sequence number of "Sn".

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**Spellbound** · 10-01-2008, 06:35 PM

Hi Leith

Thanks for the speedy response.

The code works fine, I just had to alter the B5 cell reference part of the code to a column that does not contain any data after row 361.

Regards ...spellbound