what is wrong with this single line of code (finding last row in a column)
Results 1 to 8 of 8

Thread: what is wrong with this single line of code (finding last row in a column)

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question what is wrong with this single line of code (finding last row in a column)

    Code:
    Dim cntRows As Long
    cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Cells("F1").Find("*", , , , xlByRows, xlPrevious, , , False).Row
    Trying to find the last row in Column F that is not blank.

    I get 'Invalid procedure call of argument' (run-time error '5': )

  2. #2
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: what is wrong with this single line of code (finding last row in a column)

    Try using range, instead of cells...
    Code:
    Dim cntRows As Long
    cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Range("F1").Find("*", , , , xlByRows, xlPrevious, , , False).Row
    Incidentally, if it's just the last used row you need, then you can use:
    Code:
    Dim cntRows As Long
    cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Range("F65536").end(xlup).Row
    Last edited by sykes; Jul 15th, 2019 at 12:26 PM.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Re: what is wrong with this single line of code (finding last row in a column)

    Quote Originally Posted by sykes View Post
    Try using range, instead of cells...
    Code:
    Dim cntRows As Long
    cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Range("F1").Find("*", , , , xlByRows, xlPrevious, , , False).Row
    Incidentally, if it's just the last used row you need, then you can use:
    Code:
    Dim cntRows As Long
    cntRows = ActiveWorkbook.Worksheets("ANALYTICS").Range("F65536").end(xlup).Row
    Thank you, the second one worked for what I need.

    Coincidentally, the first code returns a value of "1" (when I use MsgBox to show me the value of the variable) when there was actually 23 rows in column F.

    The second code returns the value I was expecting (23)

    Why would the first code only show "1"?

    Thanks again, sykes.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: what is wrong with this single line of code (finding last row in a column)

    It Returns 1, because you are only searching one cell (namely F1)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: what is wrong with this single line of code (finding last row in a column)

    Quote Originally Posted by Fluff View Post
    It Returns 1, because you are only searching one cell (namely F1)
    Thank you, Fluff.

    How would I go about searching starting from F1 and down to the last cell? (the 2nd code sykes provided works fine, but I would like to understand how to locate the last row going from the top and down instead of going the very last cell on the sheet and searching up...) Thank you

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: what is wrong with this single line of code (finding last row in a column)

    Like
    Code:
    Range("F1").End(xlDown).Offset(1).Row
    But if you have any blank cells in col F you might get the wrong result
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Dec 2016
    Location
    Houston, TX
    Posts
    245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: what is wrong with this single line of code (finding last row in a column)

    Quote Originally Posted by Fluff View Post
    Like
    Code:
    Range("F1").End(xlDown).Offset(1).Row
    But if you have any blank cells in col F you might get the wrong result
    That's it. Thank you. & FWIW I dont have any blanks. Column E does have blanks in it, but the procedure right before this one, I had it copy just the cells with content from column E and paste that over to column F cell 1. thanks again for your help.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: what is wrong with this single line of code (finding last row in a column)

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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