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 ?
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 ?
VBA Code:
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