AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 664
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
Hi there,
I have a basic function which I've used in a number of workbooks to return the last "used" column of a given worksheet
e.g. If the worksheet in question has data in the range $A$1:$Z$100, it will return 26 (referring to column Z)
If I then randomly put something in an arbitrary row (non-contiguous with the original data) in column AD, the function will return 30 (referring to column AD) etc. etc.
Pretty simple?
Here is the function :
I've just realised that if the sheet in question is filtered, and the filter has no results (there are no rows matching the criteria of the filter)
The function returns 1 - even though the header row is visible and there are multiple columns in the header row
In other words, I can see the header row in $A1:$Z1 but the function, seemingly, can't?
Why does the function not detect the populated cells of the header row when the data below is filtered out?
Thanks!
Al
I have a basic function which I've used in a number of workbooks to return the last "used" column of a given worksheet
e.g. If the worksheet in question has data in the range $A$1:$Z$100, it will return 26 (referring to column Z)
If I then randomly put something in an arbitrary row (non-contiguous with the original data) in column AD, the function will return 30 (referring to column AD) etc. etc.
Pretty simple?
Here is the function :
Code:
Public Function FindLastColumn(sht As Worksheet)
If WorksheetFunction.CountA(sht.Cells) > 0 Then
FindLastColumn = sht.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Else
FindLastColumn = 1
End If
End Function
I've just realised that if the sheet in question is filtered, and the filter has no results (there are no rows matching the criteria of the filter)
The function returns 1 - even though the header row is visible and there are multiple columns in the header row
In other words, I can see the header row in $A1:$Z1 but the function, seemingly, can't?
Why does the function not detect the populated cells of the header row when the data below is filtered out?
Thanks!
Al