Last used Column
 

I have been successfully using the function below since Jim Tomlinson posted
it in 2006. Recently under certain circumstances it has returned an "invalid"
value.

In a case in point:
sh has 19 columns and 70 rows
Cells 1 to 19 of Row 1 have been merged
25 rows have been filtered out using autofilter (filtered col = 19)

If I leave the filtering in place the function is returning 1
If I remove the filtering, it correctly returns 19.

Can anyone explain why and whether there are other circumstances in which it
might behave unexpectedly?

Function fnLastCol(sh As Worksheet) As Long
fnLastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function

TIA

Last used Column
 

Hi Lionel,

Under testing the problem seems to occur only if the filtered data is on
contiguous rows like Column headers on row 1 and rows 2,3,4 etc with data. If
there is at least 1 hidden row to break the displayed data then it works fine.

However, I have tested using after the last cell on the worksheet and that
seems to overcome the problem. (that does not mean that I have not introduced
another; just that I have not yet found it)

Function fnLastCol(sh As Worksheet) As Long
fnLastCol = sh.Cells.Find(What:="*", _
After:=sh.Cells(sh.Rows.Count, _
sh.Columns.Count), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function

--
Regards,

OssieMac

Last used Column
 

Hi OssieMac,

Thanks for the work.
Taking it further, the problem seems to be even more specific than that.
Having chased the problem away by breaking the sequence (hiding and unhiding
a column), I couldn't make the problem reccur without closing (without saving
(may not be relevant)) and reopening the file.

Still, for this occurrence at least, rather than mess with the function
which I use all over the place, the answer, since I remove the filter later
in the application anyway, is just to remove it a bit earlier.

Thanks again,

Lionel

"OssieMac" wrote:

Hi Lionel,

Under testing the problem seems to occur only if the filtered data is on
contiguous rows like Column headers on row 1 and rows 2,3,4 etc with data. If
there is at least 1 hidden row to break the displayed data then it works fine.

However, I have tested using after the last cell on the worksheet and that
seems to overcome the problem. (that does not mean that I have not introduced
another; just that I have not yet found it)

Function fnLastCol(sh As Worksheet) As Long
fnLastCol = sh.Cells.Find(What:="*", _
After:=sh.Cells(sh.Rows.Count, _
sh.Columns.Count), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function

--
Regards,

OssieMac