Cfrgtkky

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?

Thanks.

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, 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.

I know this is old, but I've tested this in many ways and it hasn't let me down yet, unless someone can tell me otherwise.

Row number

Row = ws.Cells.Find(What:="*", After:=[A1] , SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Column Letter

ColumnLetter = Split(ws.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)

Column Number

ColumnNumber = ws.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Try using the code after you active the sheet:

Dim J as integer

J = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

If you use Cells.SpecialCells(xlCellTypeLastCell).Row only, the problem will be that the xlCellTypeLastCell information will not be updated unless one do a "Save file" action. But use UsedRange will always update the information in realtime.

I think we can modify the UsedRange code from @Readify's answer above to get the last used column even if the starting columns are blank or not.

So this lColumn = ws.UsedRange.Columns.Count modified to

this lColumn = ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1 will give reliable results always

?Sheet1.UsedRange.Column + Sheet1.UsedRange.Columns.Count - 1

Above line Yields 9 in the immediate window.

Here's something which might be useful. Selecting the entire column based on a row containing data, in this case i am using 5th row:

Dim lColumn As Long



lColumn = ActiveSheet.Cells(5, Columns.Count).End(xlToLeft).Column

MsgBox ("The last used column is: " & lColumn)