15

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

Improve this question
2

2 Answers 2

Reset to default
15

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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.
When row number changed it says typemismatch.. can you where do i need to change for different rows
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).

Improve this answer

3 Comments

Worksheets("DTCs").Cells(1, Columns.Count).End(xlToLeft).Select
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.
Presumably, like .End(xlUp) for rows, this will also fail to handle hidden columns, 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 and could be adapted to find the last column as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.