Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Count Rows (Excel 2002)

    Hi

    I need to be able to count rows until cell value reaches a value in another cell which contains a formula.

    Please see attached sample sheet.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Rows (Excel 2002)

    See if this will help!
    Run Countrows!
    Regards,
    Rudi

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Count Rows (Excel 2002)

    Hi Rudi

    Love your reply, but I need it be placed in a cell for printing purposes.

    Sorry tobe a drag!

    Thanks for the prompt reply.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Rows (Excel 2002)

    OK,
    Try this!
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Rows (Excel 2002)

    If the macro must count hundreds of rows, you might consider adding the statements:
    Screenupdating = False just above the Do statement and
    Screenupdating = True after the loop statement to minimize screen flicker and have the macro run faster!
    Regards,
    Rudi

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Count Rows (Excel 2002)

    Hi Rudi

    This is the one, can I get it to auto update when changes are made to the data?

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Rows (Excel 2002)

    There is a worksheet events that can trigger the macro to run automatically based on a certain actions you perform. If the macro can be moved into one of these event procedures, it can be automated. The key is to move it into the correct one. When do you want the macro to trigger, ie. after a calculation occurs, or after a value on the sheet changes, etc. (See screenshot for other events!)
    If you are not to sure, tell me which is the last action you do before the macro must trigger!
    Regards,
    Rudi

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Count Rows (Excel 2002)

    Hi Rudi

    Sorry it took so long to get back , I would like it to run after a calculation occurs that would be best.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Rows (Excel 2002)

    Hi Braddy

    The code that I passed on to you should be cut and moved to the Calculate worksheet event. To do this follow these steps:
    1. Choose Tools Macro Macros
    2. Select the macro and click the edit button
    3. Select the macro and cut it
    4. In the VBA projects window, double click on the Worksheet to activate the Worksheet module
    5. In the Objects dropdown, select Worksheet
    6. In the Procedures dropdown, select Calculate
    7. Paste the macro between the Private Sub and End Sub statements.

    If a calculation occurs on the sheet, it will now automatically trigger the macro.

    Let me know if you don't comply :-)
    Regards,
    Rudi

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Count Rows (Excel 2002)

    Hi,
    You could also modify your code slightly so it becomes a function rather than a subroutine and then simply use that function in a cell, as per the attached.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Count Rows (Excel 2002)

    Hi

    Thank you very much for your help it is much appreciated.

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Count Rows (Excel 2002)

    Hi Rory

    Thanks for your input this has been most enlightening.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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