How can you find the last cell containing data in Excel automatically?

Disadvantages of the .End(xlUp) Method

• If there is a blank cell in the middle of the data, the result may be inaccurate because VBA will stop before reaching the actual last data.
• If any cells are merged, this method may not work well because Excel considers the merged cells as a single unit.

3. Finding the Last Column with Data Using .End(xlToLeft)

The .End(xlToLeft) method in Excel VBA works to find the last column containing data in a specific row.

It works similarly to pressing Ctrl + ← (left arrow) in Excel:

• Start in the rightmost column in the row.
• Move left until you find the last column with data.
• Generates the number of columns where the data was last found.

Example VBA Code to Find Last Column

The following code will find  the last column that has the data in the first row in the “Sheet1” worksheet:

Sub FindingLastColumn()
    Dim ws As Worksheet
    Dim LastCol As Long
    Set ws = ThisWorkbook.Worksheets(“Sheet1”)

    ' Finds the last column that has data in the first row
    LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    ' Displays results in Immediate Window
    Debug.Print “Last Column with Data: “ & LastCol
End Sub

Advantages of the .End(xlToLeft) Method

• It’s great to find the last column that contains the data in the table.
• It’s simple to use without requiring a lot of code.
• It can help find the header or section of an automated report.

Disadvantages of the .End(xlToLeft) Method

• If there are empty columns between the data, this method will stop before reaching the actual last column.
• As in .End(xlUp), this method does not take into account the merged columns.

4. Using Find to Accurately Detect Last-Filled Cells

The Find method in Excel VBA is one of the most effective and appropriate ways to find the last row or column that has data in a worksheet.

In contrast to .End(xlUp), which only works on one column, the Find method can search for the last cell in the entire area of the worksheet, both rows and columns.

This method is especially beneficial when you are working with complex datasets, have cells that are hidden (hidden rows/columns), or cells that contain formulas. It is also suitable for irregular datasets.

Example VBA Code to Find the Last Line Containing Data

The following code will find the last row that has data in a given worksheet using the Find method:

Sub FindingLastRow()
    Dim ws As Worksheet
    Dim LastRow As Long
    Set ws = ThisWorkbook.Worksheets(“Sheet1”)

    ' Finds the last row that has data
    On Error Resume Next
    LastRow = ws.Cells.Find(What:=“*”, _
                            After:=ws.Range(“A1”), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            ' SearchOrder:=xlByColumns, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0

    ' Displays results in Immediate Window
    Debug.Print “Last Row with Data: “ & LastRow
End Sub

Use SearchOrder:=xlByColumns to perform a search by column.