none
Only select cells with a certain accumulative sum RRS feed

  • Question

  • Dear experts,

    I have an excel as below. How can I make Excel only to select rows when the accumulative sum is above 100 for example?

    Day1 Day2
    A 23 26
    B 45 21
    C 74 38
    D 12 29
    F 30 48

    Wednesday, August 30, 2017 1:34 AM

Answers

  • Hi,

    Based on your description, you can use code to get the result. I will move your thread to Excel for developer forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by Hafiezf Friday, September 15, 2017 11:04 PM
    Thursday, August 31, 2017 6:42 AM
  • Hi Hafiezf,

    Please try below macro code.

    Sub SelectRng()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim rng As Range
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
         If (ws.Cells(i, 2) + ws.Cells(i, 3)) > 100 Then
                   If rng Is Nothing Then
                        Set rng = ws.Rows(i)
                         rng.Select
                   Else
                         Set rng = Union(rng, ws.Rows(i)) 'if there is  multiple rows accumulative sum is above 100, select multiple rows
                         rng.Select
                   End If
         End If
    Next i
    End Sub
    
    

    Best Regards,

    Terry

    • Marked as answer by Hafiezf Monday, October 9, 2017 2:32 AM
    Thursday, August 31, 2017 9:53 AM
  • Excel PivotTable
    Select only employees who have worked more than 100 hours this week.
    http://www.mediafire.com/file/d1uo2c54f1p5m6z/09_15_17b.xlsx
    http://www.mediafire.com/file/fbspqi0nncf0k7l/09_15_17b.pdf


    • Marked as answer by Hafiezf Monday, October 9, 2017 2:33 AM
    Saturday, September 16, 2017 12:07 AM

All replies

  • Hi,

    Based on your description, you can use code to get the result. I will move your thread to Excel for developer forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by Hafiezf Friday, September 15, 2017 11:04 PM
    Thursday, August 31, 2017 6:42 AM
  • Hi Hafiezf,

    Please try below macro code.

    Sub SelectRng()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim rng As Range
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
         If (ws.Cells(i, 2) + ws.Cells(i, 3)) > 100 Then
                   If rng Is Nothing Then
                        Set rng = ws.Rows(i)
                         rng.Select
                   Else
                         Set rng = Union(rng, ws.Rows(i)) 'if there is  multiple rows accumulative sum is above 100, select multiple rows
                         rng.Select
                   End If
         End If
    Next i
    End Sub
    
    

    Best Regards,

    Terry

    • Marked as answer by Hafiezf Monday, October 9, 2017 2:32 AM
    Thursday, August 31, 2017 9:53 AM
  • Hi Terry,

    Thanks for the code. I will try it once I'm back in office next Monday.

    Friday, September 15, 2017 11:06 PM
  • Excel PivotTable
    Select only employees who have worked more than 100 hours this week.
    http://www.mediafire.com/file/d1uo2c54f1p5m6z/09_15_17b.xlsx
    http://www.mediafire.com/file/fbspqi0nncf0k7l/09_15_17b.pdf


    • Marked as answer by Hafiezf Monday, October 9, 2017 2:33 AM
    Saturday, September 16, 2017 12:07 AM