Auto-numbering rows when data is entered in any cell within a given range in the row

[SysOp99]

Hello,
I am working on a data input system that copies data from several worksheets and stores it in a separate database style spreadsheet. Column A in each of the sheets is an ID column which is simply an incremented integer starting with 1. I use the number of rows in column A that have IDs to determine how many rows need to be copied. I was manually entering the IDs but if I forgot to enter one, then the data wouldn't get copied. I thought I could use a formula to accomplish an auto-number, but it seems a formula is considered the same as a value when it comes to determining the last used row. Each worksheet has a different number of columns but will have a maximum of 20 rows of data. I am thinking that I should be able to accomplish what I want to do with VBA code that fires when a change event in the row of interest occurs. Row 1 is a header row so the auto-numbers would be needed in rows 2-21 with their values being (row number - 1). My question is how do I get a single cell to populate with an auto-number (row #-1) when any other cell in that row (within a specific range of columns) becomes non-empty and then changes back to null if all data in that row is subsequently deleted. Thanks in advance for any thoughts or help on this.

 

[GraH]

Hi,

An alternative to using VBA could be to store data in a table. With tables, columns holding a formula are as it were copied down automatically, because a table resizes automatically when new data in entered in first row below the table.

 

[SysOp99]

GraH,
Thanks for the reply. At first glance this seemed like a viable alternative and I hadn't thought of using tables. I started to think through how this would work in my particular situation and I came up with a couple of limitations.

1 - the possibility exists that no data will be added to the tables in some circumstances. I don't think I can have a table with no rows (except for the header row) and preserve the formula that generates the ID field

2 - if a user enters data and then subsequently deletes it, I need the auto-number field to revert to "". I don't see how I could achieve this in a table

I'd be happy to use tables if I could address these issues. Thanks again!

 

[GraH]

1 - the possibility exists that no data will be added to the tables in some circumstances. I don't think I can have a table with no rows (except for the header row) and preserve the formula that generates the ID field

-> add the formula anyway, and it gets stored in the table definition. Then delete all rows via the context menu inside the table (mouse right click)

2 - if a user enters data and then subsequently deletes it, I need the auto-number field to revert to "". I don't see how I could achieve this in a table

-> extend with a simple if (taking my sample) =

IF([@[Some Amounts]]="";"";ROW()-ROW(Table1[[#Headers];[Row ID]]))

But, maybe VBA is better. Then be patient for those type of alternative solutions.

 

[SysOp99]

But, maybe VBA is better. Then be patient for those type of alternative solutions.

Thanks for the follow-up GraH. I'd like to try and set this up without using tables if possible. Based on the code I have already written it will be simpler, I think, to use straight ranges and VBA to generate auto-numbers. I may need to reconsider if what I am trying to do isn't feasible.

I was able to piece together code that would auto-number the rows, if any cells in the range change:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim DRange As Range
Set DRange = Range("B2:Z22")

If Not Intersect(Target, DRange) Is Nothing Then
    Range(Cells(Target.Row, 1).Address()).Value = Target.Row - 1
End If
    
End Sub

I can't figure out how to remove the auto-number if all the cells in a given row within the range become "". I played with some loops etc. but it seems to me that these change events are prone to overflow errors. Please let me know if you have any creative thoughts on how I could get the autonumber to revert to "" if no data remained in the target row. Thanks!