How do I find the last column with data?

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.

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