I'd like to delete the entire row if the cell value in column 4 does not contain the partial value "Labor"
How Can I accomplish this with a simple loop??
I'd like to delete the entire row if the cell value in column 4 does not contain the partial value "Labor"
How Can I accomplish this with a simple loop??
[vba]
Public Sub ProcessData()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If Not .Cells(i, TEST_COLUMN).Value Like "*Labor*" Then
Rows(i).Delete
End If
Next i
End With
End Sub
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Hi, How can I modify this code so that is can check for multiple partial values in the cell. So for instance check is the cell contains - "Labour", "Quantity" and "Number".
Thanks
Substitute this into xld's code:If 0 = InStr(.Cells(i, TEST_COLUMN).Value, "Labour") _ + InStr(.Cells(i, TEST_COLUMN).Value, "Quantity") _ + InStr(.Cells(i, TEST_COLUMN).Value, "Number") Then .Rows(i).Delete
A variation of both, using an array for the name list, more suitable for longer lists.
[VBA]
Option Explicit
Option Compare Text
Public Sub ProcessData()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim Arr, a, chk As Long
Application.ScreenUpdating = False
Arr = Array("Labour", "Quantity", "Number")
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
chk = 0
For Each a In Arr
chk = chk + InStr(.Cells(i, TEST_COLUMN), a)
If chk > 0 Then Exit For
Next
If chk = 0 Then Rows(i).Delete
Next i
End With
Application.ScreenUpdating = True
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
[vba]
Public Sub ProcessData()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Evaluate("SUMPRODUCT(COUNTIF(" & .Cells(i, TEST_COLUMN).Address & _
",{""*labour*"",""*quality*"",""*Number*""}))") > 0 Then
Rows(i).Delete
End If
Next i
End With
End Sub
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber