Remove rows with certain characters

zack8576

Active Member
Joined
Dec 27, 2021
Messages
271
Office Version
  1. 365
Platform
  1. Windows
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 ?

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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try this:

VBA Code:
Sub SealantConseal()
  Dim lrNew As Long, lr As Long
  Dim sr As Long, n As Double, i As Long
  Dim f As Range, rng As Range
  
  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.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
  Else
    For i = lr - 1 To 1 Step -1
      Set f = Rows(i).Find("Sealant", , xlValues, xlPart, , , False)
      If Not f Is Nothing Then
        If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i))
      End If
    Next
    
    If Not rng Is Nothing Then
      rng.Delete
    End If
    
  End If
End Sub
 
Upvote 0
Try this:

VBA Code:
Sub SealantConseal()
  Dim lrNew As Long, lr As Long
  Dim sr As Long, n As Double, i As Long
  Dim f As Range, rng As Range
 
  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.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
  Else
    For i = lr - 1 To 1 Step -1
      Set f = Rows(i).Find("Sealant", , xlValues, xlPart, , , False)
      If Not f Is Nothing Then
        If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i))
      End If
    Next
   
    If Not rng Is Nothing Then
      rng.Delete
    End If
   
  End If
End Sub
I will run this on some test files, I will let you know how it goes
 
Upvote 0
I want to remove any row that contains the keyword "Sealant"
just in column K thanks !

If only column K is to be searched, then use the following macro:

VBA Code:
Sub SealantConseal()
  Dim lrNew As Long, lr As Long
  Dim sr As Long, n As Double, i As Long
  Dim f As Range, rng As Range
 
  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.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
  Else
    For i = lr To 1 Step -1
      If InStr(1, Range("K" & i).Value, "Sealant", vbTextCompare) > 0 Then
        If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i))
      End If
    Next
   
    If Not rng Is Nothing Then
      rng.Delete
    End If
   
  End If
End Sub
--------------------------

I also show you another way to delete rows without a loop.
Any of the 2 macros can help you.

VBA Code:
Sub SealantConseal_2()
  Dim lrNew As Long, lr As Long
  Dim sr As Long, n As Double, i As Long
  Dim f As Range, rng As Range
 
  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.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
  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

----------------------
 
Upvote 0
Solution
VBA Code:
If InStr(1, Range("K" & i).Value, "Sealant", vbTextCompare) > 0 Then
        If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i))
      End If
Can you briefly explain this part of your code? I am just trying to learn as much as I can. Thanks !
 
Upvote 0
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
[/CODE]

----------------------
this is the second option you provided, can you briefly explain this part of the code ? thanks
 
Upvote 0
Can you briefly explain this part of your code? I am just trying to learn as much as I can.
VBA Code:
      If InStr(1, Range("K" & i).Value, "Sealant", vbTextCompare) > 0 Then
        If Rng Is Nothing Then Set Rng = Rows(i) Else Set Rng = Union(Rng, Rows(i))
      End If
With the InStr function it searches each cell of column K if the "Sealant" word exists, if it exists, the InStr function returns the position within the cell where the word "Sealant" begins. So if the result is greater than 0, it means that it found the word. If it is equal to 0 the word does not exist.
If the word exists, the reference to the rng object is added, so that at the end if the rng object is not empty it deletes the rows.

this is the second option you provided, can you briefly explain this part of the code ? thanks
VBA Code:
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
Replace the word "Sealant" in the cell with an error: #N/A.
The following statement Range("K1:K" & lr).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete deletes the entire row where the cells contain an error.
The statement is preceded by the On Error Resume Next instruction in the event that no cell contains an error. If this were the case, the macro sends an error, so the On Error Resume Next instruction is placed in order not to stop execution.

:giggle:
 
Upvote 0
VBA Code:
      If InStr(1, Range("K" & i).Value, "Sealant", vbTextCompare) > 0 Then
        If Rng Is Nothing Then Set Rng = Rows(i) Else Set Rng = Union(Rng, Rows(i))
      End If
With the InStr function it searches each cell of column K if the "Sealant" word exists, if it exists, the InStr function returns the position within the cell where the word "Sealant" begins. So if the result is greater than 0, it means that it found the word. If it is equal to 0 the word does not exist.
If the word exists, the reference to the rng object is added, so that at the end if the rng object is not empty it deletes the rows.


VBA Code:
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
Replace the word "Sealant" in the cell with an error: #N/A.
The following statement Range("K1:K" & lr).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete deletes the entire row where the cells contain an error.
The statement is preceded by the On Error Resume Next instruction in the event that no cell contains an error. If this were the case, the macro sends an error, so the On Error Resume Next instruction is placed in order not to stop execution.

:giggle:
thank you so much for the explanation !
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top