Automatic and updating row numbers

[DaveSClegg]

Hi

I work in an IT department and have been asked a question about an Excel sheet - they have a sheet with numbered rows (1, 2, 3, etc) but with several rows for heading and title information the first numbered row is row 5.

They have to keep working on this sheet, sometimes adding or removing rows and would like the row numbers to update automatically as they do this. I've given them the formula:

=(ROW()-4)

This is put into cell 1 of each row from row 5, so cell A5 = "1", A6 = "2" and so on. It also means that as rows are added or removed, the numbering updates. So far so good.

However, when a new row is added, the row is blank. They have to copy the first cell from another row and paste into the new row they just added. They want this to happen automatically, so as a new row is added, the first cell will right away show the row number without having to be copied in.

I doubt this is easy to do, and if it requires a load of VBA script then please dont worry about it as I dont want to waste your time writing it all! If its not possible to do, or if its only possible through using VBA then I can just tell them that and that they can stop being so lazy and cope with copying and pasting a single cell!

Thanks,

Dave

 

[SteveO59L]

As imple worksheet_change event might help. Problem is it will fire on each change. You might be better to give them a button to press to insert the row, and populate formulae etc.


Any way heres the worksheet_change code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rng
Set rng = Range("a5:a100")
For Each c In rng
If c.Value = "" Then
c.FormulaR1C1 = "=ROW()-4"
End If
Next c


End Sub

 

[MickG]

Try:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A5:A100")
[COLOR="Navy"]If[/COLOR] Application.CountBlank(Rng) > 0 And Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]With[/COLOR] Range("a5")
    .value = 1
    .AutoFill Destination:=Rng, Type:=xlFillSeries
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dezertdog]

This was just what I needed, Thanks!

 

[MickG]

You're welcome