Insert additional two rows when condition is met

[unknownymous]

Hi Guys,

I'm still learning VBA and currently working on a project. Basically, I need two add additional 2 rows if the score is zero and add a header as "Zero Score" which you may see below:

Name Team Score
Ana Volleyball 25
Ben Basketball 36
Mike Basketball 12
Che Volleyball 9


Zero Score
Sam Basketball 0
Lucy Volleyball 0



Any help will be much appreciated!

 

[Peter_SSs]

Welcome to the MrExcel board!

We cannot see your worksheet and you haven't given us a lot of detail but suppose that
- the original data is in 3 columns (A:C)
- the data is sorted by Score high to low (or at least the zero scores are at the bottom)

then you could try this in a copy of your workbook.

Sub Two_Rows()
  Dim ZeroRow As Long
  
  On Error Resume Next
  ZeroRow = Columns("C").Find(what:=0, LookIn:=xlValues, LookAt:=xlWhole, SearchFormat:=False).Row
  On Error GoTo 0
  If ZeroRow > 0 Then
    Rows(ZeroRow).Resize(2).Insert
    Cells(ZeroRow + 1, 1).Value = "Zero Score"
  End If
End Sub

If I have not understood the possible layout of your data, please give a full description and/or samples.

 

[unknownymous]

You got it right about the 3 columns ((A:C) Thanks a lot Peter!

How about if there's a Confirmation Section which can be found in Column D (either "Yes" or "No").
I also need a division for lines that falls on "No" category before I separate the "zero score"s

Thanks again for any help.

 

[unknownymous]

Sorry bout the link..

Name Team Score Confirm
Ana Volleyball 25 Y
Ben Basketball 36 Y
Mike Basketball 12 Y
Che Volleyball 9 Y


Zero Score
Sam Basketball 0 Y
Lucy Volleyball 0 Y


Unconfirmed
Leo Basketball 10 N
Mich Volleyball 5 N

 

[Peter_SSs]

Sorry bout the link..

You cannot show images from your computer. My signature block below has options. Also, best if we can see 'Before' and 'After'.

Assuming that you data is always sorted so the your data to be split off is grouped together, try this.
Note that I have made it so you can add more "split off groups" if you want.

Sub Two_Rows_v2()
  Dim FoundRow As Long
  Dim BreakData As Variant
  Dim i As Long
  
  Const BreakInfo As String = "0|Zero Score|N|Unconfirmed" '<- Add more pairs here if you want
  
  BreakData = Split(BreakInfo, "|")
  Application.ScreenUpdating = False
  For i = 0 To UBound(BreakData) Step 2
    FoundRow = 0
    On Error Resume Next
    FoundRow = Columns("C").Find(what:=BreakData(i), LookIn:=xlValues, LookAt:=xlWhole, SearchFormat:=False).Row
    On Error GoTo 0
    If FoundRow > 0 Then
      Rows(FoundRow).Resize(2).Insert
      Cells(FoundRow + 1, 1).Value = BreakData(i + 1)
    End If
  Next i
  Application.ScreenUpdating = True
End Sub

 

[unknownymous]

Awesome! Thanks again Peter!

 

[unknownymous]

Hi Peter,

Just wanted to ask..How about if I'm going to separate those "blank cells" instead of zero? Can you help me with the codes? Thank you!

 

[Peter_SSs]

Hi Peter,

Just wanted to ask..How about if I'm going to separate those "blank cells" instead of zero? Can you help me with the codes? Thank you!

This is 6 months old and well gone out of my head.

What about some sample data & expected results?

My signature block below has options. Also, best if we can see 'Before' and 'After'.