Insert Row if the Entire Row Cell has Zero

[unknownymous]

Hi Guys,

How can I insert a blank row if the entire row has zero ?

Before:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Track1[/TD]
[TD]Track2[/TD]
[TD]Track3[/TD]
[TD]Track4[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Ted[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


After:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Track1[/TD]
[TD]Track2[/TD]
[TD]Track3[/TD]
[TD]Track4[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZEROES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ted[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance for the help.

 

[unknownymous]

Hi Peter,

See more detailed illustration...

Before:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Score1[/TD]
[TD]Score2[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Score3 -14[/TD]
[TD]Note1-12[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ann[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sef[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ben[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Lei[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

After:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Score1[/TD]
[TD]Score2[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Score 3-14[/TD]
[TD]Note1-11[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ann[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sef[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]Check[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Lei[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Check[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ben[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]OK[/TD]
[/TR]
</tbody>[/TABLE]

Whereas:

*Score 3-14 range from I4:T4
*Note 1-11 range from U4:AE4

Separate Ben as his score is "Zero" from Score 1 to 14

Thanks a lot in advance!

 

[Peter_SSs]

If the columns to check are C:T (excluding those blank in the header row) then try

Sub insertRows_v3()
  Dim a As Variant, aRws As Variant, aCols As Variant
  Dim i As Long
  Dim sTest As String
  
  Const hr As Long = 4   '<- Header row
 
  Application.ScreenUpdating = False
  aRws = Evaluate("row(" & hr + 1 & ":" & Range("A" & Rows.Count).End(xlUp).Row & ")")
  aCols = Filter(Evaluate(Replace("if(len(#),column(#),""x"")", "#", "C" & hr & ":T" & hr)), "x", False)
  a = Application.Index(Cells, aRws, aCols)
  sTest = Mid(Replace(String(UBound(a, 2), "0"), 0, "|0"), 2)
  For i = UBound(a) To 2 Step -1
    If Join(Application.Index(a, i, 0), "|") = sTest Then Rows(i + hr).Insert
  Next i
  Application.ScreenUpdating = True
End Sub

 

[unknownymous]

Thanks a lot Peter!

 

[Peter_SSs]

Thanks a lot Peter!

You're welcome.

 

[unknownymous]

You're welcome.

Hi Guys,

What if i want to insert a row if both Score 1 & 2 is zero? How can I do that?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Country[/TD]
[TD]Score1[/TD]
[TD]Score2[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]Boston[/TD]
[TD]MA[/TD]
[TD]US[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Sean[/TD]
[TD]London[/TD]
[TD]N/A[/TD]
[TD]UK[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Ems[/TD]
[TD]Paris[/TD]
[TD]N/A[/TD]
[TD]FR[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance!

 

[Peter_SSs]

1. What columns are we looking at?
2. Are there other columns on the sheet with data besides the ones shown?
3. Is the order of columns likely to change? That is, will Score1 and Score2 always be in the same columns?
4. Are we adding a row above or below the row with two zeroes?

 

[LFKim2018]

Robert, a comment on your code. It may not be possible with the OP's data but it would insert a blank row above a row that contained
3, 4, -1, -6

It would also insert a blank row above a row that was already blank in those 4 columns.

In both examples above the rows don't meet the OP's stated requirement.

Mr. Peter SSs
Pardon my incursion, but may I know the meaning of OP ?
many thanks

 

[My Aswer Is This]

OP

Original Post

 

[LFKim2018]

Mr My Aswer Is This
Thank you very much

 

[unknownymous]

Hi Peter,

See below:

1. As for my example above, Ems should be separated so additional row will set as separator - that is if the Score1 & 2 is ZERO
2. No additional columns
3. The order of the column will remain the same
4. We're adding a row above the row with two zeroes.

Thank you!