none
Find the Row Number in a column with a specific value RRS feed

  • Question

  • I'm using Excel 2010

    I need a macro to find the row number that =  "  Total" 

    using ActiveSheet.UsedRange.rows.count gives me row 5, and there is sometimes multiple rows with extra data below the total Line.     

         A          B

    1  Boat      10

    2  Car        20

    3               

    4  Total     30

    5              99

    Thanks for your help


    jmk

    Monday, November 3, 2014 10:10 PM

Answers

  • Thanks Hans

    I'm getting a run time eror '91

    Object variable or with block variable not set

    I'm getting the same error for both examples 

    I added  Dim lngRow as Long , but no change

    in my testing I'm not doing anything other than opening the file


    jmk

    Something like this worked for me:

    Sub Tst()
    
    Dim fndCell As Range
    Set fndCell = ActiveSheet.Range("A:A").Find(what:="Total", lookat:=xlWhole)
    
    MsgBox fndCell.Row
    
    End Sub

    Hope this helps,

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    • Marked as answer by JMK53 Tuesday, November 4, 2014 5:51 AM
    Tuesday, November 4, 2014 12:04 AM

All replies

  • lngRow = Range("A:A").Find(What:="Total", LookAt:=xlWhole).Row

    or

    lngRow = Cells.Find(What:="Total", LookAt:=xlWhole).Row


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 3, 2014 10:23 PM
  • Thanks Hans

    I'm getting a run time eror '91

    Object variable or with block variable not set

    I'm getting the same error for both examples 

    I added  Dim lngRow as Long , but no change

    in my testing I'm not doing anything other than opening the file


    jmk

    Monday, November 3, 2014 11:53 PM
  • Thanks Hans

    I'm getting a run time eror '91

    Object variable or with block variable not set

    I'm getting the same error for both examples 

    I added  Dim lngRow as Long , but no change

    in my testing I'm not doing anything other than opening the file


    jmk

    Something like this worked for me:

    Sub Tst()
    
    Dim fndCell As Range
    Set fndCell = ActiveSheet.Range("A:A").Find(what:="Total", lookat:=xlWhole)
    
    MsgBox fndCell.Row
    
    End Sub

    Hope this helps,

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    • Marked as answer by JMK53 Tuesday, November 4, 2014 5:51 AM
    Tuesday, November 4, 2014 12:04 AM
  • Thanks Daniel - this did the trick

    jmk

    Tuesday, November 4, 2014 5:51 AM