find the last row on  a column with formula
Results 1 to 5 of 5

Thread: find the last row on a column with formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default find the last row on a column with formula



    The row 2 to row 30 of first column of sheet1 contain formula below

    =IF(ISBLANK(sheet2!D2),"",sheet2!D2)
    =IF(ISBLANK(sheet2!D3),"",sheet2!D3)
    .
    .
    =IF(ISBLANK(sheet2!D30),"",sheet2!D30)


    The thing is only row two actually have a value, from row 3 to row 30 is blank based on formula, but when i use below function
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    the lRow i got is 30

    I want to get 2 in this case.

    Anybody can help please?

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find the last row on a column with formula

    You try:
    Code:
    Debug.Print Range("A2:A30").SpecialCells(xlCellTypeConstants).Row
    Last edited by sadboy309; Apr 29th, 2019 at 09:54 AM.

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find the last row on a column with formula

    It works, thank you very much.

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: find the last row on a column with formula

    Quote Originally Posted by sadboy309 View Post
    You try:
    Code:
    Debug.Print Range("A2:A30").SpecialCells(xlCellTypeConstants).Row
    hmm... i don't think this returns the last row with data (not blank) in column A

    Maybe...
    Code:
    lRow = ActiveSheet.Columns(1).Find(what:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    M.
    Last edited by Marcelo Branco; Apr 29th, 2019 at 10:04 AM.

  5. #5
    New Member
    Join Date
    Apr 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: find the last row on a column with formula

    I just find out Debug.Print Range("A2:A30").SpecialCells(xlCellTypeConstants).Row didn't work

    yours works fine.

    Thanks,

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
  •