Crocdundee
Board Regular
- Joined
- May 10, 2010
- Messages
- 174
- Office Version
- 2013
- Platform
- Windows
I wish to delete all rows with column A:A containing this text
Hi, I am using excel 2007
Data is in Column A:A
Rows change daily
I am using an array as this
myArray = Array("Turffontein", "Hastings", "Sha Tin","Kenilworth", "Thirsk")
My code deletes other rows which is not in the array
Dim myArray As Variant
Dim LR As Long
Dim j As Integer
Dim i As Integer
Application.ScreenUpdating = False
With Sheets("Sheet1")
myArray = Array("Turffontein", "Hastings", "Sha Tin", _
"Kenilworth", "Thirsk")
LR = .Range("D" & .Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
On Error Resume Next
With Range("D" & i)
j = Application.Match(.Value, myArray, True)
If Err <> 0 Then
Err.Clear
.EntireRow.Delete
End If
End With
Next i
End With
Application.ScreenUpdating = True
end sub
any help will be appreciated as usual
Graham
Hi, I am using excel 2007
Data is in Column A:A
Rows change daily
I am using an array as this
myArray = Array("Turffontein", "Hastings", "Sha Tin","Kenilworth", "Thirsk")
My code deletes other rows which is not in the array
Dim myArray As Variant
Dim LR As Long
Dim j As Integer
Dim i As Integer
Application.ScreenUpdating = False
With Sheets("Sheet1")
myArray = Array("Turffontein", "Hastings", "Sha Tin", _
"Kenilworth", "Thirsk")
LR = .Range("D" & .Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
On Error Resume Next
With Range("D" & i)
j = Application.Match(.Value, myArray, True)
If Err <> 0 Then
Err.Clear
.EntireRow.Delete
End If
End With
Next i
End With
Application.ScreenUpdating = True
end sub
any help will be appreciated as usual
Graham