Finding the last used column

[Lionel H]

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.

 

[Gary''s Student]

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

 

[Jim Thomlinson]

I can not get it to return anything but 53... that being said you can try
changing xlFormulas to xlValues and see if that helps...

Function fnLastCol(sh As Worksheet) As Long
On Error Resume Next
fnLastCol = sh.Cells.Find(what:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function