I currently have an Excel spreadsheet where I have a row number in column "A" running down the spreadsheet.
Is there a way that I can get excel to automatically update this number, (and also subsequent row numbers) after a row has been inserted?Any help gratefully received.
Thanks,
Paul Caddick
Replies (10)
Please login or register to join the discussion.
Automatically update formula
Dear Paul,
Try this:
Assuming that your sequential numbering begins at A6 and the first number is 1 then in A7 type this formula:
ROW()-5
Copy this formula to cover the extant of your data.
When you do insert a row, simply copy the formula from the cell above. All the other cells below would have automatically changed.
Note that the 5 above represents the unused cells above the first numbered cell.
Good luck,
Ashraf
Renumbering after Inserting a row
I assume that you want the rows numbered so that you can do things like sorting and resorting back into the original order, in which case the numbers need to be values, not formulae.
Below is a quick and dirty solution which assumes that your table starts on Excel row 6
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/03/2009 by Spiney Norman
'
'
ActiveWorkbook.Names.Add Name:="Top", RefersTo:="=A6"
Range("Top").Select
' Move to the next row and note it for Autofill later
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveWorkbook.Names.Add Name:="Top1", RefersTo:=ActiveCell
' Now find the empty gap where the row was inserted
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
' We are now at the inserted row, put a 'z' in the empty gap
ActiveCell.FormulaR1C1 = "z"
' Now find the bottom of the range to be renumbered
Selection.End(xlDown).Select
ActiveWorkbook.Names.Add Name:="Bottom", RefersTo:=ActiveCell
' Do the renumbering
Range("Top:Top1").Select
Selection.AutoFill Destination:=Range("Top:Bottom")
End Sub
Simple solution
On the trial you carried out before, instead of copying the formula down the whole spreadsheet, just edit the new row cell to the one above + 1, all the other cells will renumber themselves.
Silly Question
This might seem like a silly questions, but what is the purpose of having the row numbers in the spreadsheet itself. Surely you have the row numbers to the side of the sheet, and if you want these to show when printing, then could you not just set the row and column heading in the page set-up.
If it relates to a record number, then would you not be better to use access as you can get the table to automatically assign a unique number whenever data is inserted, then yoou can odbc this back to your spreadsheet for analysis etc...
Just my 2pensc worth.
Use =row() This returns the row number for the row the cell is in. If you insert a row, it updates. You could also do an offset by adding 1 to row (), to give =row()+1.
Heavy handed VBA option
I think this works (seems to on a limited test)
In the VB editor for the Worksheet you want the numbers to appear in Col A paste the following code
'code starts
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tRows 'holds number of rows selected
Dim r ' loop counter
If Target.Column = 1 Then 'make sure the change is in Col A
With Target
tRows = Target.Rows.Count 'count the rows selected
For r = 0 To tRows 'loop through each selected cell in Col A
'If cell A? is blank
If Target.Cells(r, 1) = "" Then _
Target.Cells(r, 1).FormulaR1C1 = "=ROW()" 'add the formula returning the row number in cell A? Amended to use ROW instead of "=CELL(""row"",RC)"
Next r 'loop
End With
Calculate ' added to recalculate formulae.
End If 'if change not in Col A do nothing
End Sub
'code ends
This may have unexpected effect on other things you do when copy/ pasting for example.
EDIT - by the way this is only to automate adding the CELL formula to blank cells when you insert rows. As in later comments if the old formulae moved down by the insert do mot updat it probably means Calculation is set to Manual
autoupdate =CELL("row",A1)
If you insert rows between then the reference should change - so for example insert a row at a27 and what was a27 becomes a28, and the formula in that cell changes to =CELL("row",A28). All the formula does is give you the row number of the cell referenced in the formula.
Perhaps you have calculation set to manual? In which case inserting a row will have no effect until you press recalc.
It all depends!
Without going into detail, it does all depend on what you've got and what you're trying to achieve. I'm also assuming your numbers in col "A" are sequential.
If you want Excel to update automatically, then you'll have to use VBA. You'll need a procedure which pre-empts the "right click" event on your mouse (assuming you do right click on the grey border to insert new rows), inserts a row and also a value / formula into the new row. VBA could be used to update all superseding rows but it would probably be easier to have a formula in col "A" which refers to the previous row, eg: A7=A6+1, and then normal Excel functionality does the rest.
Avoiding VBA, can you achieve what you want simply by using a formula like the one I suggested and manually copying into the new row? If your numbers in col "A" are related to the row numbers in some way, then you could also use the Row() function to return a value.
how about
=CELL("row",A1) returns a 1. put it in a1 and then copy it down and you get row numbers as you describe. insert a row and they update. You have to copy it into the new row!