VBA - delete row if cell contains

[sherwood16]

Hi all,

Trying to search this but having no luck.

I'm after some VBA where I can delete the whole row of a table, if column C contains the word "Charge"?

Can someone help?

Thanks.

 

[Trevor G]

Welcome to the forum,

This will delete each row in column C where it meets your criteria.

Sub myDeleteRows()
Dim MyCol As String
Dim i As Integer
For i = 1 To Range("C" & "65536").End(xlUp).Row Step 1
If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), "Charge") > 0 Then
Range("C" & i).EntireRow.Delete
End If
Next i

End Sub

 

[sherwood16]

Excellent - just what I was after!

Thanks

 

[Trevor G]

You are welcome and thanks for the feedback.

 

[ebucskibur]

This is really helpful! Was wondering however if there is a way to update the code in order to apply this to the whole worksheet?

At the moment if I have say, 2 rows where the 'Delete' criteria has been met I have to run the maco twice (once to remove each row).

Any advice much appreciated!

 

[ZVI]

Reverse order of For i = LastRow To 1 Step -1 can help.
For example try this:

' Deleting entire rows with MyTarget
Sub myDeleteRows1()
  
  Const MyTarget = "Charge" ' <-- change to suit
  
  Dim Rng As Range
  Dim i As Long, j As Long
  
  ' Calc last row number
  j = Cells.SpecialCells(xlCellTypeLastCell).Row  'can be: j = Range("C" & Rows.Count).End(xlUp).Row
  
  ' Collect rows with MyTarget
  For i = j To 1 Step -1
    If WorksheetFunction.CountIf(Rows(i), MyTarget) > 0 Then
      If Rng Is Nothing Then
        Set Rng = Rows(i)
      Else
        Set Rng = Union(Rng, Rows(i))
      End If
    End If
  Next
  
  ' Delete rows with MyTarget
  If Not Rng Is Nothing Then Rng.Delete
  
  ' Update UsedRange
  With ActiveSheet.UsedRange: End With
  
End Sub

 

[ZVI]

More safe & fast version:

' Deleting entire rows with MyTarget
Sub myDeleteRows2()
  
  Const MyTarget = "Charge" ' <-- change to suit
  
  Dim Rng As Range, DelCol As New Collection, x
  Dim i As Long, j As Long, k As Long
  
  ' Calc last row number
  j = Cells.SpecialCells(xlCellTypeLastCell).Row  'can be: j = Range("C" & Rows.Count).End(xlUp).Row
  
  ' Collect rows range with MyTarget
  For i = 1 To j
    If WorksheetFunction.CountIf(Rows(i), MyTarget) > 0 Then
      k = k + 1
      If k = 1 Then
        Set Rng = Rows(i)
      Else
        Set Rng = Union(Rng, Rows(i))
        If k >= 100 Then
          DelCol.Add Rng
          k = 0
        End If
      End If
    End If
  Next
  If k > 0 Then DelCol.Add Rng
  
  ' Turn off screen updating and events
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  ' Delete rows with MyTarget
  For Each x In DelCol
    x.Delete
  Next
  
  ' Update UsedRange
  With ActiveSheet.UsedRange: End With
  
  ' Restore screen updating and events
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  
End Sub

 

[ebucskibur]

Many thanks for you response ZVI! Your comments within the code were especially helpful. Have a great day : )

 

[ZVI]

Many thanks for you response ZVI! Your comments within the code were especially helpful. Have a great day : )

Welcome to MrExcel, ebucskibur!
And have fun

 

[ilieradum]

This Marco worked for me.

Thanks you!