Consider:

Function fnLastCol(sh As Worksheet) As Long
fnLastCol = 0
n = Columns.Count
For i = n To 1 Step -1
If Application.WorksheetFunction.CountA(sh.Columns(i) ) < 0 Then
fnLastCol = i
Exit Function
End If
Next
End Function

tested with:

Sub main()
Dim sh As Worksheet
Set sh = Sheets("Sheet1")
MsgBox (fnLastCol(sh))
End Sub

--
Gary''s Student - gsnu2007k


"Lionel H" wrote:

All, FYI, I have been using the following (offered by Jim Thomlinson in 2006)
without trouble for years:
Function fnLastCol(sh As Worksheet) As Long
On Error Resume Next
fnLastCol = sh.Cells.Find(what:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Recently however, I received a worksheet from a colleague structured as:
A:AK populated, AL:AY empty, AZ:BA populated
So the function should have returned 53 (BA) but was in fact returning 37
(AK) .

Only when I noticed that columns J:AK were grouped and removed the grouping
could I make the function behave as I expected.