Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
Search for a column based on the column header and then past data from it to another column in another workbook | Excel Programming | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Programming | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
How can i have all alike product codes in column A be matched with like cities in column B and then add the totals that are in column C | Excel Programming |