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

Question

I have an excel sheet in which I need to find the last non empty cell in a specific row.

How do I do this?

The below will select this for me, but it will select the first not empty cell, I need the last not empty cell in the row #29.

Worksheets("DTCs").Range("A29").End(xlToRight).Select

Possible duplicate of [Error in finding last used cell in VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — M--, Jul 07 '17 at 14:24
This is _not_ a duplicate of [Error in finding last used cell in VBA](https://stackoverflow.com/q/11169445/1026), nor [How can I find last row that contains data in the Excel sheet with a macro?](https://stackoverflow.com/q/71180/1026), since this talks about finding the right-most **column**, while those questions are focussed on finding the last **row**, but the general comments and ideas from the answers to those questions apply here as well. — Nickolay, Apr 22 '18 at 23:18

brettdj · Answer 1 · 2014-02-02T05:02:50.870

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

Note that compaisons like `ws.Cells(1, Columns.Count) = vbNullString` will raise an error if the cell contains an error value like `#N/A`. Also note that the `.Find` call changes the defaults in the Excel's Find dialog, which is why I avoid using it. See [the list of gotchas like these I've compiled](https://stackoverflow.com/a/49971540/1026). — Nickolay, Apr 22 '18 at 23:29
When row number changed it says typemismatch.. can you where do i need to change for different rows — ChanGan, Jun 04 '20 at 13:33

score 12 · Accepted Answer · answered Feb 02 '11 at 09:29

12

I think it might work just search from the other direction, so something like:

Worksheets("DTCs").Range("IV29").End(xlToLeft).Select

Though maybe the IV would need to be changed to something else depending on the version of Excel (this seems to work in 2003).

answered Feb 02 '11 at 09:29

Hans Olsson

54,199
15
94
116

8

`Worksheets("DTCs").Cells(1, Columns.Count).End(xlToLeft).Select` – Tim Williams Feb 02 '11 at 23:55
2

Tim's comment works for all Excel versions and is the superior approach. The only exceptions are when the row is completely empty, or an extreme case where the entire row is filled. Both these situtaions will result with A1, which should trigger a further test to validate the true last used cell. – brettdj Dec 21 '11 at 00:29
Presumably, [like `.End(xlUp)` for rows, this will also fail to handle hidden columns](https://stackoverflow.com/a/49971540/1026), and a more robust solution would be to get the rightmost column from `UsedRange` and work your way to the left to find the column with data. [My solution does this to find the last row](https://stackoverflow.com/a/49971492/1026) and could be adapted to find the last column as well. – Nickolay Apr 22 '18 at 23:22

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

2 Answers2

Linked

Related