excel-vba Methods for Finding the Last Used Row or Column in a Worksheet Find the Last Non-Empty Cell in a Row
Example
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
