使用 Excel VBA 检测非空的最后一个单元格位置

Detect non empty last cell location using Excel VBA

提问by Mahender

In my Excel sheet, I have VBA code to detect the last non-empty cell in Column A and add incremental serial number value in that cell (in below example cell A6 value should be SN104).

在我的 Excel 工作表中，我有 VBA 代码来检测 A 列中的最后一个非空单元格并在该单元格中添加增量序列号值（在下面的示例中，单元格 A6 值应为 SN104）。

This processing is limited only to Column A, and in this image example first non-empty last cell is at A6, sometimes it can be after 100 cells or 1000 cells.

此处理仅限于 A 列，在此图像示例中，第一个非空的最后一个单元格位于 A6，有时可能在 100 个单元格或 1000 个单元格之后。

Is there any simple way to handle this scenario?

有没有简单的方法来处理这种情况？

采纳答案by Steve Mallory

Something like

就像是

Dim lngLastUsedRow as Integer
lngLastUsedRow = Range("A65536").End(xlUp).Row

Dim lngFirstEmptyRow as Integer
lngFirstEmptyRow = Range("A65536").End(xlUp).Offset(1,0)

// do your increment
newValue = Cint(Mid(CurrentWorkSheet.Range("A" + lngLastUsedRow).Value,2)) + 1

CurrentWorkSheet.Range("A" & lngFirstEmptyRow).Value = "SN" + newValue

I don't have excel on me, I can't test it right now. But this should get you started.

我没有excel，我现在无法测试。但这应该让你开始。

回答by Alpha

Public Function GetLastCell(ByVal startRng as Range) as Range

    With startRng
        Set GetLastCell = IIf(.Offset(1).Value = "", .Offset(0), .End(xlDown))
    End With

End Function

For your example, you can define a Range variable rng, and call the above function in this way:

对于您的示例，您可以定义一个 Range 变量 rng，并以这种方式调用上述函数：

Dim rng as Range
Set rng = GetLastCell( Range("A1") )

Then rng is referring to the last cell of Column A

然后 rng 指的是 A 列的最后一个单元格

回答by brettdj

Something like this which

像这样的东西

1. Find the true last used cell in any Excel version, and handles a blank result

2. Parses the string in the last non-blank cell (handling any length of alpha then numeric)to update the next blank cell

   Sub GetTrueLastCell()
   Dim rng1 As Range
   Dim objRegex As Object
   Dim strFirst As String
   Set rng1 = Columns("A").Find("*", [a1], xlFormulas)
   If Not rng1 Is Nothing Then
       Set objRegex = CreateObject("vbscript.regexp")
       With objRegex
           .Pattern = "^(.+?[^\d])(\d+)$"
           If .test(rng1.Value) Then
               strFirst = .Replace(rng1.Value, "")
               rng1.Value = strFirst & (Val(Right$(rng1.Value, Len(rng1.Value) - Len(strFirst)) + 1))
           End If
   
       End With
   Else
       MsgBox "Data range is blank"
   End If
   End Sub
   

1. 在任何 Excel 版本中查找上次使用的真实单元格，并处理空白结果

2. 解析最后一个非空白单元格中的字符串（处理任意长度的字母和数字）以更新下一个空白单元格

   Sub GetTrueLastCell()
   Dim rng1 As Range
   Dim objRegex As Object
   Dim strFirst As String
   Set rng1 = Columns("A").Find("*", [a1], xlFormulas)
   If Not rng1 Is Nothing Then
       Set objRegex = CreateObject("vbscript.regexp")
       With objRegex
           .Pattern = "^(.+?[^\d])(\d+)$"
           If .test(rng1.Value) Then
               strFirst = .Replace(rng1.Value, "")
               rng1.Value = strFirst & (Val(Right$(rng1.Value, Len(rng1.Value) - Len(strFirst)) + 1))
           End If
   
       End With
   Else
       MsgBox "Data range is blank"
   End If
   End Sub
   

回答by Knom

Assumptions:

假设：

• Next cell in list is empty
• Serial N's only have three digits after 'SN' string (i.e., if it reaches 1000, earlier ones don't need padding, like '0100'

• 列表中的下一个单元格为空
• 序列号 N 在 ' SN' 字符串后只有三位数字（即，如果达到 1000，则较早的不需要填充，例如 ' 0100'

-

——

Dim rAll As Range, rLast As Range, rNext As Range, iNextSN As Integer

Set rAll = Intersect(Sheet1.Cells(1).CurrentRegion, Sheet1.Columns(1)) ' Column 'A' can be contiguous with others
Set rLast = rAll.Cells(rAll.Cells.Count) ' Last cell in current list
Set rNext = rLast.Offset(1) ' Next cell below current list

iNextSN = CInt(Right(rLast.Value, 3)) ' Get value of last serial N
rNext.Value = "SN" & iNextSN + 1 ' Assemble next SN with increment

-

——