Detect non empty last cell location using Excel VBA

Question

enter image description here

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).

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.

Is there any simple way to handle this scenario?

This is *such* a duplicate. [Example](http://stackoverflow.com/a/6304419/119775). Next time, please try doing a simple [search](http://stackoverflow.com/search?q=[vba]+last+empty+cell) before asking a question that has already been answered multiple times. — Jean-François Corbett, Apr 14 '12 at 08:44

Alpha · Answer 1 · 2012-04-16T03:03:06.873

1

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:

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

Then rng is referring to the last cell of Column A

edited Apr 16 '12 at 03:03

answered Apr 13 '12 at 23:59

Alpha

2,372
3
21
23

Note that this works like the "CTRL+Down" shortcut in that it stops on blank cells and ignores hidden rows. – Nickolay Apr 20 '18 at 20:08

score 0 · Accepted Answer · answered Apr 13 '12 at 23:31

0

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.

answered Apr 13 '12 at 23:31

Steve Mallory

4,245
1
28
31

1

This presumes the use of Excel 2003 (65536 rows) rather than catering for all Excel versions. It does also lock into the strings always have a two digit alpha prefix - which although reasonable given the question should be catered for (likewise a blank result) – brettdj Apr 14 '12 at 02:56

score 0 · Answer 3 · answered Apr 14 '12 at 02:53

Something like this which

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

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, "$1")
            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

Note that due to the use of `.Find()` this resets the defaults in the Find dialog to "look in formulas" — Nickolay, Apr 20 '18 at 20:04

score 0 · Answer 4 · answered Mar 09 '20 at 14:03

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'

-

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

-

Detect non empty last cell location using Excel VBA

4 Answers4