excel-vba Methods for Finding the Last Used Row or Column in a Worksheet Find the Last Non-Empty Cell in a Row


In this example, we will look at a method for returning the last non-empty column in a row.

This method will work regardless of empty regions within the data set.

However caution should be used if merged cells are involved, as the End method will be "stopped" against a merged region, returning the first cell of the merged region.

In addition non-empty cells in hidden columns will not be taken into account.

Sub FindingLastCol()
    Dim wS As Worksheet, LastCol As Long
    Set wS = ThisWorkbook.Worksheets("Sheet1")
    'Here we look in Row 1
    LastCol = wS.Cells(1, wS.Columns.Count).End(xlToLeft).Column
    Debug.Print LastCol
End Sub