DaveMiddleton

Is there a formula or way to re-number an item in eg a list of items 1-400 so that ALL the subsequent numbers are also re-numbered if you remove one of the items from the list?? HELP!!!
Currently I am manually changing each number if this necessity happens.

nkh

Not completely sure I understand but try this.

Enter =Row(A6) into cell A6 and you get the row number 6
Copy this formula down all the cells and you have automatic numbering.

Obviously you could add an offset if you wanted to start you data not at the first row or from a different number.

Column will return the column number (not letter) if your data is horizontl not vertical.

Da Booga

If i've understood right all you need to do is add one to the cell value above i.e. for cell A2 enter the following "=A1+1".

You only need to enter the number 1 in cell A1 then the above in cell 2 then you can just drag the formula down as required.

this will only change the numbers if you completely delete a line that you remove & not just clear the cells.

HTH

Gareth

markr1963

But if you just wanted to clear the cells you could use a bit of code like this attached to a macro:

Selection.ClearContents
h = ActiveCell.Row 'row number of cell just cleared
Selection.End(xlUp).Select
i = ActiveCell.Value 'value of cell immediately above that just cleared
ActiveCell.SpecialCells(xlLastCell).Select
j = ActiveCell.Row 'row number of the last row in list
l = i + 1 ' the next number
For k = h + 1 To j ' loop from cell immediately below that just cleared
If Cells(k, 1).Value = "" Then ' if cell is blank skip it
Else
Cells(k, 1).Value = l 'next number
l = l + 1
End If

Next

HTH

Mark