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.

 

[ZVI]

This Marco worked for me.

I am glad you find the code useful, welcome aboard!

 

[MineExcel]

Hey, I'm pretty sure this code is the base of what I'm looking for. I just need it to only search a specific column for the MyTarget variable, (i have at least two columns that could display the target data, but I only want to remove rows based on one of them). Is this an additional For function?

 

[Lalo2DaG]

This VBA code works great! However, is there a way to modify this code to delete rows if they don't equal certain criteria's?

 

[kalak]

This VBA code works great! However, is there a way to modify this code to delete rows if they don't equal certain criteria's?

The obvious thing to try first is to modify Vlad's code as:

' 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 'changed from > 0
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

'But adequate other alternatives if need be.

 

[Lalo2DaG]

Kalak - Thank you!. I'm not very advanced when it comes to VBA code for Excel, I can do some basic stuff. I was wondering if there was one other little tweak to this. I don't want to delete the first 7 rows. The first 7 rows contain miscellaneous information regarding the report and includes the header. Is this still possible?

 

[kalak]

Kalak - Thank you!. I'm not very advanced when it comes to VBA code for Excel, I can do some basic stuff. I was wondering if there was one other little tweak to this. I don't want to delete the first 7 rows. The first 7 rows contain miscellaneous information regarding the report and includes the header. Is this still possible?

Modifying VBA codes written by others can be a somewhat dodgy activity except in pretty simple cases. Even for codes as well explained as Vlad's (ZVI's).

What you request seems pretty straightforward though, and the modification I'd suggest is to make the changes in red. I haven't tested it though, and if anything doesn't work for you it could be a good idea to start a new thread.

' Deleting entire rows with MyTarget
Sub myDeleteRows3()

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  'change to For i = 8 to j  if want to exclude 1st 7 rows
    If WorksheetFunction.CountIf(Rows(i), MyTarget) = 0 Then 'changed from > 0
        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

 

[Madbie]

My skill level is enough to copy VBA and make adjustments in the code to suit my specific needs. My skill level is not enough to copy VBA and add additional code to perform every task I need. This code works perfectly for me. I changed the target to delete any row where column A = "Planned"
My question is how do I loop this code to delete any row where column A = "Planned", "Planned:", "Pre Route Time:", "Route Departure Time:", "Delivery", and "Return To Depot:"?

The column contains unformatted numbers (the rows I want to keep), the above text (the rows I want to delete), and one additional string that is text & date (a row that I want to keep).

This forum has been awesome but this is my first post. I hope I provided enough/the right information and haven't missed this solution somewhere else.

Thanks for any reply,
Toby

 

[Madbie]

My skill level is enough to copy VBA and make adjustments in the code to suit my specific needs. My skill level is not enough to copy VBA and add additional code to perform every task I need. This code works perfectly for me. I changed the target to delete any row where column A = "Planned"
My question is how do I loop this code to delete any row where column A = "Planned", "Planned:", "Pre Route Time:", "Route Departure Time:", "Delivery", and "Return To Depot:"?

The column contains unformatted numbers (the rows I want to keep), the above text (the rows I want to delete), and one additional string that is text & date (a row that I want to keep).

This forum has been awesome but this is my first post. I hope I provided enough/the right information and haven't missed this solution somewhere else.

Thanks for any reply,
Toby

So I played around with this and just realized this code deletes any line where any of the text appears regardless of column. My next question would be How do I confine the search to just Column A and ignore "Planned:" for instance in Column B?

 

[kalak]

Here's another code you might like to try.

Sub delete_some_rows()

Dim X As Object, target, a, s
Dim r&, c&, i&, j&, u&
Set X = CreateObject("scripting.dictionary")
target = Array("Planned", "Planned:", "Pre Route Time:", "Route Departure Time:", "Delivery", "Return To Depot:")

If Cells(1) = "" Then Cells(1) = Chr(2)
a = ActiveSheet.UsedRange
r = UBound(a, 1): c = UBound(a, 2)

For Each s In target
    X(s) = 1
Next s

For i = 1 To r
    If Not X(a(i, 1)) = 1 Then
        u = u + 1
        For j = 1 To c
            a(u, j) = a(i, j)
        Next j
    End If
Next i

Cells(1).Resize(r, c).ClearContents
Cells(1).Resize(u, c) = a
If Cells(1) = Chr(2) Then Cells(1).ClearContents

End Sub

 

[Madbie]

I love this forum!!!

Thank you very much, did exactly what I needed to do. I will use this code to try and delete any line where "Cancelled" is in Column P. I am assuming I have to modify the 'UBound' but I'll mess around with it for a while until I get stuck again.

Thanks for your help.