Last used Column

[Lionel H]

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

 

[OssieMac]

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

 

[Lionel H]

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