Excel VBA:自动为每一行生成唯一编号 [英] Excel VBA : Auto Generating Unique Number for each row

问题描述

我有一个要求，要自动从某个值(例如1000)开始用唯一的数字填充行.

I got a requirement for auto filling the row with unique number starting from some value(say 1000).

• 工作表有2列.

• The sheet has 2 columns.

每当我在每一行的第二列中填充值时，第一列应自动填充.

When ever I fill a value in 2nd column in each row the first column should auto filled.

我已经填写了3行(1000,1001,1002).

I have filled 3 rows(1000,1001,1002).

现在，如果我删除自动生成的值为1001的中间行，则最初具有值1002的行将自动更新为1001.

Now if i delete the middle row that has the auto generated value as 1001, the row that was initially with the value 1002 gets automatically updated to 1001.

但是根据我的要求，该值应保持唯一(应保持为1002).

But according to my requirement the value should remain unique(it should remain as 1002).

我使用的公式是:

=IF(B2<>"",COUNTA($B$2:B2)+999,"")

我的excel在删除值之前是这样的:

My excel is like this before deleting the value:

Test_Case_Id    Name
1000             a
1001             b
1002             c

删除中间行后，它变为:

After deleting the middle row it becomes:

Test_Case_Id    Name
1000             a
1001             c

预期结果:

Test_Case_Id    Name
1000             a
1002             c

请帮助我.

谢谢， 韦韦克

推荐答案

您已使用VBA标记，但未使用VBA.重新计算工作表时，对行进行计数或使用当前行号的公式将始终更新.

You tagged with VBA, but you are not using VBA. Formulas that count rows or use the current row number will always update when the sheet re-calculcates.

为了实现您所描述的内容，您确实需要VBA.一个简单的工作表更改事件就可以做到这一点.按照

In order to achieve what you describe, you really need VBA. A simple worksheet change event should do it. Think along the lines of

Private Sub Worksheet_Change(ByVal Target As Range)
Dim maxNumber
If Not Intersect(Target, Range("B:B")) Is Nothing Then
' don't run when more than one row is changed
    If Target.Rows.Count > 1 Then Exit Sub
' if column A in the current row has a value, don't run
    If Cells(Target.Row, 1) > 0 Then Exit Sub
' get the highest number in column A, then add 1 and write to the
' current row, column A
    maxNumber = Application.WorksheetFunction.Max(Range("A:A"))
    Target.Offset(0, -1) = maxNumber + 1
End If
End Sub

复制代码，右键单击工作表标签，选择查看代码"，然后粘贴到代码窗口中.

Copy the code, right-click the sheet tab, select "view code" and paste into the code window.

以下是删除ID为1002的行之前和之后的屏幕截图:

Here's a screenshot of before and after deleting row with ID 1002:

这篇关于Excel VBA:自动为每一行生成唯一编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！