Insert Rows Based on criteria (or CountIf?)
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Insert Rows Based on criteria (or CountIf?)

  1. #1
    New Member
    Join Date
    May 2014
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert Rows Based on criteria (or CountIf?)

    Hello,

    I have two tabs, a summary tab, and a Data Tab. The Data Tab is organized like this (from rows 2 to 300 - with each person having a different number in Column E).

    Column B Column E
    Person 1 (row 2) 10
    Person 2 (row 3) 2
    Person 3 (row 4) 0
    Person 4 (row 5)... 1
    Person 5 ...(row 300) 0

    I would like to add rows on the Summary tab (starting in row 4) equal to the total number of people on the Data tab that have a value in Column E > 0.

    Therefore, using the example above, the macro would add in 3 new rows (starting on row 4) on the Summary tab, because there are 3 people on the Data Tab that have values in Column E > 0.

    Thanks!

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,686
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Insert Rows Based on criteria (or CountIf?)

    Try this:
    Code:
    Sub Copy_Me_Maybe()
    'Modified  7/19/2019  6:49:38 AM  EDT
    Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrowa As Long
    Dim Lastrow As Long
    Sheets("Data").Activate
    Lastrow = Sheets("Data").Cells(Rows.Count, "E").End(xlUp).Row
    For i = 2 To Lastrow
        If Cells(i, "E").Value > 0 Then
            Lastrowa = Sheets("Summary").Cells(Rows.Count, "E").End(xlUp).Row + 1
            If Lastrowa < 4 Then Lastrowa = 4
            Rows(i).Copy Sheets("Summary").Rows(Lastrowa)
        End If
    Next
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,686
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Insert Rows Based on criteria (or CountIf?)

    Or try this using Filter:
    Code:
    Sub Filter_Me_Please()
    'Modified  7/19/2019  7:38:46 AM  EDT
    Application.ScreenUpdating = False
    Dim lastrow As Long
    Sheets("Data").Activate
    Dim c As Long
    Dim s As Variant
    c = 5 ' Column Number Modify this to your need
    s = ">" & 0 'Search Value Modify to your need
    lastrow = Cells(Rows.Count, c).End(xlUp).Row
    Dim Counter As Long
    With ActiveSheet.Cells(1, c).Resize(lastrow)
        .AutoFilter 1, s
        Counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
        If Counter > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Summary").Rows(4)
        Else
            MsgBox "No values found"
        End If
        .AutoFilter
    End With
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  4. #4
    New Member
    Join Date
    May 2014
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert Rows Based on criteria (or CountIf?)

    Sorry,

    After re-reading my initial question I may have been a little too vague on my request. Let me try re-phrasing again!

    I have two tabs, a summary tab and a data tab. The Summary Tab is constructed like this:

    Column A Column B Column C Column D Column E
    Title Title Title Title Title Row #1
    Blank (N/A) Blank (N/A) Blank (N/A) Blank (N/A) Blank (N/A) Row #2
    Header 1 Header 2 Header 3 Header 4 Header 5 Row #3
    Data Data Data Data Data Row #4
    Data Data Data Data Data Row #5
    Subtotal Subtotal Subtotal Subtotal Subtotal Row #6


    The Data Tab is Constructed like this (note - it only goes up until row 300)

    Column A Column B Column C Column D Column E
    Title A People Title Title Amount Row #1
    Data Person 1 Data Data $500.00 Row #2
    Data Person 2 Data Data $0.00 Row #3
    Data Person 3 Data Data $400.00 Row #4
    Data Person 4 Data Data $0.00 Row #5
    Data Person 300 Data Data $300.00 Row #300

    My goal is to:

    1. Count the number of persons (column B - Data Tab) that have Amounts (Column E - Data Tab) > $0.00. So, for this example, there are 3 persons who have amounts > $0.00.
    2. On the Summary Tab, insert blank rows, below row #4 , equal to the number of persons that have amounts > $0.00 on the Data Tab.


    The end result of the Summary Tab would be this:

    Column A Column B Column C Column D Column E
    Title Title Title Title Title Row #1
    Blank (N/A) Blank (N/A) Blank (N/A) Blank (N/A) Blank (N/A) Row #2
    Header 1 Header 2 Header 3 Header 4 Header 5 Row #3
    Data Data Data Data Data Row #4
    Data Data Data Data Data Row #5
    (Newly inserted blank row) (Newly inserted blank row) (Newly inserted blank row) (Newly inserted blank row) (Newly inserted blank row) Row #6
    (Newly inserted blank row) (Newly inserted blank row) (Newly inserted blank row) (Newly inserted blank row) (Newly inserted blank row) Row #7
    (Newly inserted blank row) (Newly inserted blank row) (Newly inserted blank row) (Newly inserted blank row) (Newly inserted blank row) Row #8
    Subtotal Subtotal Subtotal Subtotal Subtotal Row #9


    Please let me know if this helps clarify my original question.

    Thank you!
    Last edited by hutch27; Jul 19th, 2019 at 12:59 PM.

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,686
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Insert Rows Based on criteria (or CountIf?)

    Try this:
    Code:
    Sub Insert_Rows()
    'Modified  7/19/2019  5:33:32 PM  EDT
    Application.ScreenUpdating = False
    Dim lastrow As Long
    Sheets("Data").Activate
    lastrow = Cells(Rows.Count, "E").End(xlUp).Row
    Dim ans As Long
    ans = Application.WorksheetFunction.CountIf(Range("E1:E" & lastrow), ">0")
    Sheets("Summary").Rows(4).Resize(ans).Insert
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  6. #6
    New Member
    Join Date
    May 2014
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert Rows Based on criteria (or CountIf?)

    I believe this function of the code is deriving an answer from the Summary Tab - how would i change it so that the answer counts all values > 0 on the Data tab?

    ans = Application.WorksheetFunction.CountIf(Range("E1:E" & lastrow), ">0")

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,686
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Insert Rows Based on criteria (or CountIf?)

    If you see on line 5 of the code it says Sheets("Data") activate

    So the count is coming from column E of sheet named Data

    So your saying it's not working properly?

    It looks down column E on sheet named Data for values greater then 0 to the last row with data in column E

    Not just to row 300

    Are you sure you only want it to look to row 300 my script looks to last row in column E with data.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    New Member
    Join Date
    May 2014
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert Rows Based on criteria (or CountIf?)

    To answer your last question, yes, I would only want it to look to row 300.

    However, it seems the code is not working properly. I re-tested it and am able to confirm that the number of rows added on the Summary Tab is equal to the total number of cells in Column E, on the Summary Tab (not the Data Tab), that are > 0
    Last edited by hutch27; Jul 19th, 2019 at 07:21 PM.

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,686
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Insert Rows Based on criteria (or CountIf?)

    Try this:
    I test all my scripts and it worked for me but try this:
    Code:
    Sub Insert_Rows()
    'Modified  7/19/2019  7:25:22 PM  EDT
    Application.ScreenUpdating = False
    Dim lastrow As Long
    lastrow = 300
    Dim ans As Long
    ans = Application.WorksheetFunction.CountIf(Sheets("Data").Range("E1:E" & lastrow), ">0")
    Sheets("Summary").Rows(4).Resize(ans).Insert
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    New Member
    Join Date
    May 2014
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert Rows Based on criteria (or CountIf?)

    Great, this one seems to be working as intended - I appreciate the quick follow up and I hope I was clear with my explanations!

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
  •