Remove rows with certain characters

[zack8576]

smart people of Mrexcel, I have this code to calculate the total amount of a certain item, and display the total after the last row in the excel.
after this row is added after the last row, I want to remove any row that contains the keyword "Sealant" (these can be any row, any number of rows), except the newly added row
what is the best approach to this problem, to ensure the new row will not be removed ?

Sub SealantConseal()
    Dim lrNew As Long, lr As Long
    lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    lrNew = lr
    sr = 2

    With Range("M1", Cells(Rows.Count, "M").End(3))
        .Replace What:="""", Replacement:=vbNullString, LookAt:=xlPart
        .Replace What:="/JOINT", Replacement:=vbNullString, LookAt:=xlPart
        
    n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*JOINT SEALANT*")
    lrNew = lrNew + 1
    Cells(lrNew, "A") = Cells(lr, "A")
    Cells(lrNew, "B") = "."
    Cells(lrNew, "C") = Int(((n / 12 / 14.5) + 0.5) * 2)
    Cells(lrNew, "D") = "F51019"
    Cells(lrNew, "I") = "Purchased"
    Cells(lrNew, "K") = "CS-102 Sealant"
    If Cells(lrNew, "C").Value = 0 Then
        Rows(lrNew).Delete
    End If
End Sub

 

[Peter_SSs]

Are you sure that your code logic is correct?

    If Cells(lrNew, "C").Value = 0 Then
        Rows(lrNew).Delete

This deletes the new line if the value in column C is zero. The only way that column C can be zero is if the SUMIFS calculation comes to -87
That seems very odd to me and I'm wondering if you really mean to delete that new row if the SUMIFS calculation comes to zero (ie n = 0)?

 

[zack8576]

Are you sure that your code logic is correct?

    If Cells(lrNew, "C").Value = 0 Then
        Rows(lrNew).Delete

This deletes the new line if the value in column C is zero. The only way that column C can be zero is if the SUMIFS calculation comes to -87
That seems very odd to me and I'm wondering if you really mean to delete that new row if the SUMIFS calculation comes to zero (ie n = 0)?

Peter, the macros are for cleaning up a large number of csv files. These csv then get imported into another software .
Any item with a qty of 0 does not need to be imported, so I can either delete the whole row, or remove the item # (removing the item # will prevent this item from getting imported as well)
one of the benefits of removing the whole new row, is that it is easier (less items to look at) for the users to check the files before importing

 

[zack8576]

Are you sure that your code logic is correct?

    If Cells(lrNew, "C").Value = 0 Then
        Rows(lrNew).Delete

This deletes the new line if the value in column C is zero. The only way that column C can be zero is if the SUMIFS calculation comes to -87
That seems very odd to me and I'm wondering if you really mean to delete that new row if the SUMIFS calculation comes to zero (ie n = 0)?

Another way to approach this problem, is to simply not generate the new row at all when the qty is 0, this saves the headache of having to remove a row that just got added to the file.
With other peoples' help, I did try this route, but the code I had kept adding new row to the file even when the qty is 0

 

[Peter_SSs]

Any item with a qty of 0

Nothing in the code mentions qty, so I have no way of knowing what is meant by that.

My point was that the only way that
Cells(lrNew, "C").Value = 0
is if
Int(((n / 12 / 14.5) + 0.5) * 2) = 0
and the only way that
Int(((n / 12 / 14.5) + 0.5) * 2) = 0
is if
n = -87

Since n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*JOINT SEALANT*")
that seemed very odd to me.

 

[zack8576]

Nothing in the code mentions qty, so I have no way of knowing what is meant by that.

My point was that the only way that
Cells(lrNew, "C").Value = 0
is if
Int(((n / 12 / 14.5) + 0.5) * 2) = 0
and the only way that
Int(((n / 12 / 14.5) + 0.5) * 2) = 0
is if
n = -87

Since n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*JOINT SEALANT*")
that seemed very odd to me.

Peter, I saw this same problem when I was running some tests. I've removed the + 0.5, and tweaked a couple of numbers to get to the ideal results.

 

[Peter_SSs]

I've removed the + 0.5, and tweaked a couple of numbers to get to the ideal results.

Glad to hear you have a good outcome. Could you share the final code?

 

[zack8576]

Glad to hear you have a good outcome. Could you share the final code?

of course, see below for full code

Sub SealantConseal()
  Dim lrNew As Long, lr As Long
  Dim sr As Long, n As Double, i As Long
  lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
  lrNew = lr
  sr = 2

  With Range("M1", Cells(Rows.Count, "M").End(3))
    .Replace what:="""", Replacement:=vbNullString, Lookat:=xlPart
    .Replace what:="/JOINT", Replacement:=vbNullString, Lookat:=xlPart
  End With

  n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*JOINT SEALANT*")
  lrNew = lrNew + 1
  Cells(lrNew, "A") = Cells(lr, "A")
  Cells(lrNew, "B") = "."
  Cells(lrNew, "C") = Int((n / 12 / 14) * 2)
  Cells(lrNew, "D") = "F51019"
  Cells(lrNew, "I") = "Purchased"
  Cells(lrNew, "K") = "CS-102 Sealant"
  If Cells(lrNew, "C").Value = 0 Then
    Rows(lrNew).Delete
  Else

    Range("K1:K" & lr).Replace "*Sealant*", "#N/A", xlWhole
    On Error Resume Next
    Range("K1:K" & lr).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End If
End Sub

 

[Peter_SSs]

see below for full code

Thanks. I was just going to suggest some possible changes that you may want to consider, though keeping the existing working code would be fine too.

You mentioned earlier one of the things that I had in mind

Another way to approach this problem, is to simply not generate the new row at all when the qty is 0, this saves the headache of having to remove a row that just got added to the file.

The other thing that I was going to mention is that if you want, the whole new line can be entered at once rather than one cell at a time.

Sub SealantConseal_a()
  Dim lr As Long, sr As Long, n As Double, i As Long
  
  lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
  sr = 2
  With Range("M1", Cells(Rows.Count, "M").End(3))
    .Replace what:="""", Replacement:=vbNullString, Lookat:=xlPart
    .Replace what:="/JOINT", Replacement:=vbNullString, Lookat:=xlPart
  End With

  n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*JOINT SEALANT*")
  If Int((n / 12 / 14) * 2) <> 0 Then
    Cells(lr + 1, "A").Resize(1, 11).Value = _
      Array(Cells(lr, "A"), ".", Int((n / 12 / 14) * 2), "F51019", "", "", "", "", "Purchased", "", "CS-102 Sealant")
    Range("K1:K" & lr).Replace "*Sealant*", "#N/A", xlWhole
    On Error Resume Next
    Range("K1:K" & lr).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End If
End Sub

BTW, I wasn't exactly correct when I made the statement below.
However, it seems like the focus on it has picked up something that wasn't quite right so "all's well that ends well".

The only way that column C can be zero is if the SUMIFS calculation comes to -87

 

[zack8576]

Thanks. I was just going to suggest some possible changes that you may want to consider, though keeping the existing working code would be fine too.

You mentioned earlier one of the things that I had in mind

The other thing that I was going to mention is that if you want, the whole new line can be entered at once rather than one cell at a time.

Sub SealantConseal_a()
  Dim lr As Long, sr As Long, n As Double, i As Long
 
  lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
  sr = 2
  With Range("M1", Cells(Rows.Count, "M").End(3))
    .Replace what:="""", Replacement:=vbNullString, Lookat:=xlPart
    .Replace what:="/JOINT", Replacement:=vbNullString, Lookat:=xlPart
  End With

  n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*JOINT SEALANT*")
  If Int((n / 12 / 14) * 2) <> 0 Then
    Cells(lr + 1, "A").Resize(1, 11).Value = _
      Array(Cells(lr, "A"), ".", Int((n / 12 / 14) * 2), "F51019", "", "", "", "", "Purchased", "", "CS-102 Sealant")
    Range("K1:K" & lr).Replace "*Sealant*", "#N/A", xlWhole
    On Error Resume Next
    Range("K1:K" & lr).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End If
End Sub

BTW, I wasn't exactly correct when I made the statement below.
However, it seems like the focus on it has picked up something that wasn't quite right so "all's well that ends well".

Peter, looking at your code
And I just love the whole idea of adding the whole new row at once instead of adding one cell at a time

I will apply this to many other parts of the macro as well, I think using array will make the entire macro easier to manage as well.

Thanks !

 

[Peter_SSs]

You're welcome.