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

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:

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

---

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:

Code:

---

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)

change after to last cell of worksheet, then it will start from a1

personally, i would not be using find to determine usedrange

Quote:

---

Originally Posted by westconn1

change after to last cell of worksheet, then it will start from a1

---

Alright, I changed the code for calculating the first row to:

Code:

---

First_Row = XL_Ws.Cells.Find(what:="*", After:=XL_Ws.Cells(Rows.Count, Columns.Count), SearchDirection:=xlNext, SearchOrder:=xlByRows).Row

---

This works, but I don't know why. I would have expected this to return "Nothing" to First_Row since there are no rows after the last cell in the worksheet.

Can you please explain what this is doing?

Quote:

---

Originally Posted by westconn1

personally, i would not be using find to determine usedrange

---

What do you recommend using then?

Coincidentally, I found another one of your posts Westconn in a similar thread, but your suggestion for finding the First used row seems highly inadequate:

Code:

---

firstrow = range("a9:f99").row

---

I even expanded the range to the entire worksheet, and it returns row 1 as the first row used on a worksheet where the first row is empty:

Code:

---

Range(Cells(1, Columns.Count), Cells(Rows.Count, 1)).Row

---

I am looking for a robust solution that does not:

• involve specifying any particular row/column to find the first/last column/row (e.g. I do not want to find the last row in column '5' - I want the very last non-empty row of data in the worksheet);
• involve either xlCellTypeLastCell or UsedRange variables as these are flawed (link here); and
• behave incorrectly if rows/columns are hidden (although I can work around this last one) which rules out using functions like Last_Row = .Cells(.Rows.Count, "A").End(xlUp).Row

The only solution I have found which can accomplish this is using the Find Function.

Westconn, what method do you use to find these variables?

Quote:

---

I am looking for a robust solution that does not:

---

i always use

vb Code:

nextrow = cells(rows.count, col).end(xlup).row + 1

where col is a column that will always have a value i each filled row (generally 1, for col A), but i seldom have hidden rows, especially at end of data

Quote:

---

This works, but I don't know why. I would have expected this to return "Nothing" to First_Row since there are no rows after the last cell in the worksheet.

---

by default find will continue at the beginning, after reaching the end of worksheet, so if you start at the end find will find at the beginning

Quote:

---

I even expanded the range to the entire worksheet, and it returns row 1 as the first row used on a worksheet where the first row is empty:

---

this in fact works with the usedrange of a sheet, but in the thread linked to, the question did not specify used cell in range (though on rereading, that may have been intent)

and being picky,

Quote:

---

First_Row = XL_Ws.Cells.Find(what:="*", After:=XL_Ws.Cells(Rows.Count, Columns.Count), SearchDirection:=xlNext, SearchOrder:=xlByRows).Row

---

rows.count and columns.count should be XL_ws.rows.count etc, in event the activesheet was in 2003 workbook, where the XL_ws workbook was later version result could be unpredictable, or the other way round would error

Cool, thanks for the feedback Westconn.

The main reason I don't use the .end() function is because I often work with data in which rows or columns may only have one or a few values, thereby giving the incorrect index. Also, I usually do not know in advance which row/column to search.

I think I'll go with the Find function and your suggestion of changing the 'After' argument to the last cell (and yes, you're right to be picky - I should qualify the counts with the worksheet name) as it seems to do everything I want, and it calculates them in the blink of an eye.

So just to summarise for anyone reading this thread, the solution I ended up using was:

Code:

---

Private Sub Test2()

    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:="*", After:=XL_Ws.Cells(XL_Ws.Rows.Count, XL_Ws.Columns.Count), _
            SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
        First_Col = XL_Ws.Cells.Find("*", After:=XL_Ws.Cells(XL_Ws.Rows.Count, XL_Ws.Columns.Count), _
            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

---

This will find the first and last rows and columns. Additionally:

• It does not require any particular row/column to be specified in the search - it will search the entire worksheet for the very first/last row/column);
• It is not affected by hidden rows/columns; and
• It is efficient and compact.

Thanks to Westconn for the help. :)

Just what I was looking for! Thanks to Earlien and Westconn1

I'm glad someone else found this useful too :)