Find the Last Cell in a Row with Null Data-CodePudding

I need to find the last cell that contains data in a row that has blank cells. I have tried:

Dim rowCell as Integer
rowCell = Cells.Find(what:="*", _
   after:=Range("A1"), _
   lookat:=xlPart, _
   LookIn:=xlFormulas, _
   SearchOrder:=xlByColumns, _
   SearchDirection:=xlPrevious, _
   matchCase:=False).Column

Debug.Print rowCell

But it returns "5" and the columns go up to HE, which is supposed to be Column 213. How can I get it to return a value for one row?

EDIT: I was able to get this to work on one row with 158 columns, but when I try it on the line below it, by incrementing the after:=Range("A2"), it gives me 6. It's supposed to be 213. Line 58 goes up to FB, which is supposed to be 158, but the script reports 213.

CodePudding user response：

Figured it out.

Dim rowCell as Integer
For i = 5 To 54
    With ActiveSheet
        If .Rows(i).EntireRow.Hidden Then
            
        Else
            .Rows(i).Select
            rowCell = ActiveSheet.Rows(i).Find(what:="*", _ 
               lookat:=xlPart, _
               LookIn:=xlFormulas, _
               searchorder:=xlByColumns, _
               SearchDirection:=xlPrevious, _
               MatchCase:=False).Column

            Debug.Print "Row " & i; " Column " & rowCell
        End If
    End With
Next i

CodePudding user response：

Print Last Columns Using the Find Method

Option Explicit

Sub PrintLastColumns()
    
    Dim LastCell As Range
    Dim LastColumn As Long
    Dim i As Long
    
    For i = 5 To 54

        With ActiveSheet.Rows(i)
            ' Not needed in the Find method (in this case):
            ' After - refers to the first cell by default.
            ' LookAt - 'xlWhole' or 'xlPart' is not relevant
            ' since you search for anything ('*').
            ' SearchOrder - not relevant when in one row or one column.
            ' MatchCase - 'False' by default; not relevant ('*').
            'Set LastCell = .Find(What:="*", LookIn:=xlFormulas, _
                SearchDirection:=xlPrevious)
            ' or simply:
            Set LastCell = .Find("*", , xlFormulas, , , xlPrevious)
        End With
            
        If LastCell Is Nothing Then ' no last cell hence no last column
            Debug.Print "Row " & i & " is empty."
        Else
            LastColumn = LastCell.Column
            Debug.Print "Row: " & i, "Last Column: " & LastColumn, _
                "Last Cell Address: " & LastCell.Address(0, 0)
        End If

    Next i

End Sub