Auto-update row numbers after inserting new row

[carusso]

I have a spreadsheet with Column A numbered sequentially using auto fill
series. If someone adds a new row between 2 existing numbered rows, is there
any way to automatically update the row numbering?

Thanks!

 

[Pete_UK]

If you put this formula in A1:

=ROW()

and copy it down as far as you need, then it will always show the
correct row. If you insert a new row, however, then the new row will
not have the formula in it.

Hope this helps.

Pete

 

[carusso]

Thanks for your response, Pete. I have tried the Row() feature also. But I
need some way [if it exists! ;-)] to have the row numbers automatically
update after the new row is added. The spreadsheet is being used by people
that really don't have a clue what to do with Excel, and the SAS program that
will be reading the spreadsheet requires the correct row number be in the
spreadsheet.

Cheryl

 

[Pete_UK]

Well, to do that you would need a macro, and if you do a Google search
I'm sure you will find many suggested.

But, if you go that route and if your users are not very competant
with Excel then how could you ensure that they would enable macros to
run when they start Excel?

Pete

Thanks for your response, Pete.  I have tried the Row() feature also.  But I
need some way [if it exists! ;-)] to have the row numbers automatically
update after the new row is added.  The spreadsheet is being used by people
that really don't have a clue what to do with Excel, and the SAS program that
will be reading the spreadsheet requires the correct row number be in the
spreadsheet.

Cheryl
--
carusso

I have a spreadsheet with Column A numbered sequentially using auto fill
series.  If someone adds a new row between 2 existing numbered rows, is there
any way to automatically update the row numbering?

Thanks!

- Show quoted text -

 

[carusso]

Well, unfortunately, I can't guarantee they would enable macros. If they see
the dialog box to enable/disable macros (which as a default...most probably
still have this feature turned on), it probably scares them and they click
"disable"...lol
--
carusso

Well, to do that you would need a macro, and if you do a Google search
I'm sure you will find many suggested.

But, if you go that route and if your users are not very competant
with Excel then how could you ensure that they would enable macros to
run when they start Excel?

Pete

Thanks for your response, Pete. I have tried the Row() feature also. But I
need some way [if it exists! ;-)] to have the row numbers automatically
update after the new row is added. The spreadsheet is being used by people
that really don't have a clue what to do with Excel, and the SAS program that
will be reading the spreadsheet requires the correct row number be in the
spreadsheet.

Cheryl
--
carusso

I have a spreadsheet with Column A numbered sequentially using auto fill
series. If someone adds a new row between 2 existing numbered rows, is there
any way to automatically update the row numbering?

Thanks!

- Show quoted text -

 

[Gord Dibben]

Double-click event code to insert a row and have the number update when
using the =ROW() function in column A

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
If Target.Column = 1 And Target.Row <> 1 Then
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Formula
End If
Cancel = True
End Sub


Have a contingency plan that will render the workbook unusable if they
disable macros.

Here is a sample................

Create a sheet named Dummy with a large message typed in the middle.

"You have disabled Macros and this workbook is useless without them. Please
close and re-open with macros enabled"

Then add these two event codes to Thisworkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = True
Sheets("Dummy").Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP