Excel – Detect non empty last cell location using Excel VBA

excelvba

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?

Best Answer

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

Related Solutions

Excel – Last not empty cell (column) in the given row; Excel VBA

I have expanded on my comment above to provide solutions that

do not use Select
cater for the last cell in row 1 being used
cater for the entire row being empty
cater for the entire row being full

The Find method in the second code is a far more direct method of establishing the first non-blank cell

This line Set rng1 = ws.Rows(1).Find("*", ws.[a1], xlValues, , xlByColumns, xlPrevious) says, start in cell A1 of Sheet "DTCa" then look backwards (ie from the last cell in row 1) in row1 by column looking for anything (the *). This method either find the last non blank or returns Nothing , ie an empty row

using xltoLeft with specific checks

Sub Method1()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("DTCs")
If ws.Cells(1, Columns.Count) = vbNullString Then
    Set rng1 = ws.Cells(1, Columns.Count).End(xlToLeft)
    If rng1.Column <> 1 Then
        'return last used cell
        MsgBox "rng1 contains " & rng1.Address(0, 0)
    Else
    If ws.[a1] = vbNullString Then
            MsgBox ws.Name & " row1 is completely empty", vbCritical
        Else
            'true last used cell is A1
            MsgBox "rng1 contains " & rng1.Address(0, 0)
        End If
    End If
Else
    'last cell is non-blank
    MsgBox ws.Cells(1, Columns.Count) & " contains a value", vbCritical
End If
End Sub

recommended

Sub Method2()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Sheets("DTCs")
    Set rng1 = ws.Rows(1).Find("*", ws.[a1], xlFormulas, , xlByColumns, xlPrevious)
    If Not rng1 Is Nothing Then
        MsgBox "rng1 contains " & rng1.Address(0, 0)
    Else
        MsgBox ws.Name & " row1 is completely empty", vbCritical
    End If
End Sub

Excel – Last non-empty cell in a column

Using following simple formula is much faster

=LOOKUP(2,1/(A:A<>""),A:A)

For Excel 2003:

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

It gives you following advantages:

it's not array formula
it's not volatile formula

Explanation:

(A:A<>"") returns array {TRUE,TRUE,..,FALSE,..}
1/(A:A<>"") modifies this array to {1,1,..,#DIV/0!,..}.
Since LOOKUP expects sorted array in ascending order, and taking into account that if the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range (in our case {1,1,..,#DIV/0!,..}) that is less than or equal to the value (in our case 2), formula finds last 1 in array and returns corresponding value from result_range (third parameter - A:A).

Also little note - above formula doesn't take into account cells with errors (you can see it only if last non empty cell has error). If you want to take them into account, use:

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

image below shows the difference:

enter image description here

Best Answer

Related Solutions

Excel – Last not empty cell (column) in the given row; Excel VBA

Excel – Last non-empty cell in a column

Related Topic