Function to find the last used column in a sheet returning incorrectly (XL2013)

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
657
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. 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 :

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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Without using a function, try this code to find the last used column.
Code:
Dim lCol As Long
lCol = ActiveSheet.UsedRange.Columns.Count
 
Upvote 0
Mmmm, don't like UsedRange to be honest - too many quirks (e.g. what if there's nothing in A1? just one example...)
 
Upvote 0
How about starting from a different cell:

Code:
        With sht
            FindLastColumn = .Cells.Find(What:="*", After:=.Cells(1, .Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        End With
 
  • Like
Reactions: AOB
Upvote 0
Okay that seems to work - thanks Steve!

I'm still troubled as to why the original method fails with filtered data?

The visible header row should be sufficient for it to detect the right-most column

(Actually, even if cells are hidden, by filtering or otherwise, it should still work)

I've been using it for years across loads of workbooks - now I'm worried it's not as consistent as I thought it was
 
Upvote 0
I cant seem to get this to fail regardless of any filtering filters or not:

Code:
Public Function FindLastColumn(sht As Worksheet)
FindLastColumn = sht.Cells.Find(What:="*", After:=sht.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column
End Function

Nope thats no good!
 
Last edited:
Upvote 0
So far ive yet to see a different result using this if the filter is on or not:

Code:
Public Function FindLastColumn(sht As Worksheet)
FindLastColumn = sht.Cells.Find(What:="*", After:=sht.Cells(sht.Rows.Count, sht.Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End Function
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top