Results 1 to 8 of 8

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

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

    Resolved [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)
    Last edited by Earlien; Jan 16th, 2012 at 12:58 AM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,903

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

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

    personally, i would not be using find to determine usedrange
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    Quote Originally Posted by westconn1 View Post
    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 View Post
    personally, i would not be using find to determine usedrange
    What do you recommend using then?

  4. #4

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    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?

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,903

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

    I am looking for a robust solution that does not:
    i always use
    vb Code:
    1. 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

    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

    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,
    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    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.

  7. #7
    Member
    Join Date
    Oct 2010
    Location
    Bristol
    Posts
    36

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

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

  8. #8

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    I'm glad someone else found this useful too

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width