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

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

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

I have expanded on my comment above to provide solutions that

• 쓰지 않는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

그Find두번째 코드에서 메소드는 첫번째 비공백 셀을 확립하는 훨씬 더 직접적인 방법입니다.

이 선Set rng1 = ws.Rows(1).Find("*", ws.[a1], xlValues, , xlByColumns, xlPrevious)시트 "DTCa"의 셀 A1에서 시작하여 열별로 1행의 (1행의 마지막 셀에서) 뒤를 돌아보고 모든 것을 찾으라는 메시지가 표시됩니다.*이 메서드는 마지막 비어 있지 않은 것을 찾거나 반환합니다.Nothing, 빈 행

사용.xltoLeft구체적인 수표로

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

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

ReferenceURL : https://stackoverflow.com/questions/4872512/last-not-empty-cell-column-in-the-given-row-excel-vba