current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite

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?

share|improve this question
    
This is such a duplicate. Example. Next time, please try doing a simple search before asking a question that has already been answered multiple times. – Jean-François Corbett Apr 14 '12 at 8:44

3 Answers 3

up vote 0 down vote accepted

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.

share|improve this answer
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 2:56
up vote 1 down vote 
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

share|improve this answer
up vote 0 down vote

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, "$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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.