Search code examples
excelvba

How do I find the last column with data?

I've found this method for finding the last data containing row in a sheet:

ws.Range("A65536").End(xlUp).row

Is there a similar method for finding the last data containing column in a sheet?

Solution

  • Lots of ways to do this. The most reliable is find.

    Dim rLastCell As Range

Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

MsgBox ("The last used column is: " & rLastCell.Column)

    If you want to find the last column used in a particular row you can use:

    Dim lColumn As Long

lColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    Using used range (less reliable):

    Dim lColumn As Long

lColumn = ws.UsedRange.Columns.Count

    Using used range wont work if you have no data in column A. See here for another issue with used range:

    See Here regarding resetting used range.