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

SysOp99

New Member
Joined
Dec 25, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

RowIDWithTables.gif
 

SysOp99

New Member
Joined
Dec 25, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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) =
Excel Formula:
IF([@[Some Amounts]]="";"";ROW()-ROW(Table1[[#Headers];[Row ID]]))

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

SysOp99

New Member
Joined
Dec 25, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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:

VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top