Remove rows with certain characters

Z

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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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
DanteAmor said:
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
Click to expand...
I will run this on some test files, I will let you know how it goes
 
Upvote 0
zack8576 said:
I want to remove any row that contains the keyword "Sealant"
Click to expand...
zack8576 said:
just in column K thanks !
Click to expand...

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
DanteAmor said:
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
Click to expand...
Can you briefly explain this part of your code? I am just trying to learn as much as I can. Thanks !
 
Upvote 0
DanteAmor said:
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]

----------------------
Click to expand...
this is the second option you provided, can you briefly explain this part of the code ? thanks
 
Upvote 0
zack8576 said:
Can you briefly explain this part of your code? I am just trying to learn as much as I can.
Click to expand...
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.

zack8576 said:
this is the second option you provided, can you briefly explain this part of the code ? thanks
Click to expand...
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
DanteAmor said:
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:
Click to expand...
thank you so much for the explanation !
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
VBA calculates numbers incorrectly (basic arithmetic)
Replies
9
Views
670
Alex Blakenburg
A
Z
  • Solved
VBA not copying cell value when running it on certain files
Replies
2
Views
488
zack8576
Z
Z
  • Solved
Equation returns 0 when it should not (VBA)
3 4 5
Replies
42
Views
2K
zack8576
Z
Z
  • Question Question
Method or Data member not found
2
Replies
12
Views
1K
zack8576
Z
Z
  • Question Question
How to use COUNTIF
Replies
1
Views
407
zack8576
Z

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

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