# Number Sequence problem

#### jimmyglass

##### New Member
Hi,
I have a problem which I cant find the answer too looking through the old posts.

The data I am dealing with is for bills of materials for various products;
along the lines of - [Part number; Sequence No; Operation: Material; Qty] in separate columns.

I have created a macro which deletes rows from the structure if the Qty column is equal to zero.

My problem is that after the rows that arent needed have been deleted, the sequence column is no longer correct as numbers in the sequence are missing. I need a way of them autocorrecting.

To complicate things there are a number of structures on one worksheet all following on from each other, and they are not always the same length, with each structure normally numbering between 5 and 25 rows.

The first row of each structure always contain 1 in the sequence column and is never deleted, and the last row in a structure always contains "Bookin" or "Stockin" in the operation column, so these could be used to define a range where the numbers in between have to be sequential.

I hope that makes sense!

I've tried using various formulas in the sequence column such as adding 1 to the cell above but this just results in #REF in the row below a deleted row.

Any help would be greatly appreciated!!

Jimmy

Forgot to mention I'm using Excel 97

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### GlennUK

##### Well-known Member
Code:
``=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1``

#### jimmyglass

##### New Member
Yeah that works! But unfortunately it merges two columns together? and so overwrites the data in column D.

#### GlennUK

##### Well-known Member
You said:
I've tried using various formulas in the sequence column such as adding 1 to the cell above but this just results in #REF in the row below a deleted row.
... so where did you try my formula? I thought you'd be putting it in the sequence column.

So what do you mean by "it merges two columns together"?

#### jimmyglass

##### New Member
I apologise, I've got it working now, I'm not sure what I did the first time!

Thanks for that, was a lot simpler than I thought it would be!

Jimmy

#### GlennUK

##### Well-known Member
Great! I'm glad that's working now.

Replies
4
Views
193
Replies
2
Views
176
Replies
1
Views
320
Replies
3
Views
256
Replies
1
Views
228

1,190,800
Messages
5,982,984
Members
439,810
Latest member
phobo3s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back