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:

The values for each variable should be:Code: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

- 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:

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).Code:First_Row = XL_Ws.Cells.Find("*", After:=[A1], SearchDirection:=xlNext, SearchOrder:=xlByRows).Row

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)