[RESOLVED] [VBA Excel] Actual used range - Find function not working as expected

[Earlien]

I've seen many people ask how to find the "actual" used range of a worksheet on other forums, because the method that most people would initially think of will includes cells that have had values deleted, and cells that have been formatted. (See here for more details example code which is proven to be not reliable).

The solution that I found about a year ago was to use the Find function with the wild character "*" (see code below). I've used this in many macros that I've written since then but only now discovered that its not performing as expected.

To test my code and see what I'm talking about, open a new Excel workbook, and put values in worksheet 1 (any values will do - I will use X):

A1: X
A2:C4: X's in each cell

Put the following code in a new module and run it:

Private Sub Test()

    Dim XL_Ws As Excel.Worksheet
    Dim First_Row As Long, Last_Row As Long
    Dim First_Col As Integer, Last_Col As Integer
    
    Set XL_Ws = ThisWorkbook.Worksheets(1)
    
    'Check there is at least one non-empty cell
    If WorksheetFunction.CountA(XL_Ws.Cells) > 0 Then
        'Determine actual used range of worksheet
        First_Row = XL_Ws.Cells.Find(what:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
        First_Col = XL_Ws.Cells.Find(what:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
        Last_Row = XL_Ws.Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        Last_Col = XL_Ws.Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    End If
    
End Sub

The values for each variable should be:

• First_Row: 1
• First_Col: 1
• Last_Row: 4
• Last_Col: 3

But for some reason the value for First_Row is 2, not 1.

Up till now I had just used the code above which I ripped from somewhere on the internet (like this site) without understanding the components of the Find function. I decided to look into this function in more depth and found that some people will specify the second argument (an optional argument) as follows:

First_Row = XL_Ws.Cells.Find("*", After:=[A1], SearchDirection:=xlNext, SearchOrder:=xlByRows).Row

I'm guessing that After:=[A1] means it does not actually look in cell A1. So if the only value you have in the first row is in A1, it gets overlooked. (Up till now, I have obviously had more data in row 1 so I never noticed this before).

So my question is, what (if anything) can be done to make the Find function look in A1 too?

(I realise I could just test if cell A1 has something in it separately, but would like to know if the Find function can in fact include A1).


EDIT: If there is a better method than using the Find function, this would be even better - but it must be robust (see Post #4 bullet points)