Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-update row numbers after inserting new row
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! -- carusso |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-update row numbers after inserting new row
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 On Jan 23, 11:46*pm, carusso wrote: 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! -- carusso |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-update row numbers after inserting new row
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 "carusso" wrote: 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! -- carusso |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-update row numbers after inserting new row
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 On Jan 26, 4:03*pm, carusso wrote: 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 "carusso" wrote: 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! -- carusso- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-update row numbers after inserting new row
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 "Pete_UK" wrote: 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 On Jan 26, 4:03 pm, carusso wrote: 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 "carusso" wrote: 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! -- carusso- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-update row numbers after inserting new row
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 On Mon, 26 Jan 2009 08:47:03 -0800, carusso wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto update formula when inserting additional columns | Excel Worksheet Functions | |||
Inserting a row (update) | Excel Discussion (Misc queries) | |||
Auto Inserting numbers | Excel Worksheet Functions | |||
Auto Update upon shifting, inserting or deleting | Excel Worksheet Functions | |||
excel links update not working in auto, calculations in auto | Excel Worksheet Functions |