Delete Rows if Value in Column A

[ssinghal]

I want to go through a sheet with 15000 rows of data and delete rows if the value in Column A is "Lakeland" or "Stratford" with a macro

[mikerickson]

Sub test()
With ThisWorkbook.Sheets("sheet1")
.Range("A:A").Insert
With Range(.Cells(1, 2), .Cells(.Rows.Count, 2).End(xlUp))
With .Offset(0, -1)
.FormulaR1C1 = "=1/--not(OR(RC[1]=""Lakeland"",RC[1]=""Stratford""))"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
End With
End With
.Range("A:A").Delete
End With
End Sub

[ssinghal]

that doesnt work-get errors

[marshybid]

that doesnt work-get errors
Try this then, code provided to me by xld, I have edited to contain the 2 values you want to remove


'-----------------------------------------------------------------
Public Sub DeleteRowsUsingAutofilter()
'-----------------------------------------------------------------
Const TestColumn As Long = 1
Dim cRows As Long

'first, count the rows to operate on
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row

'finally, apply the autofilter for al matching cells
Columns(TestColumn).AutoFilter Field:=1, Criteria1:="Lakeland", Criteria2:="Stratford", Operator:=xlOr

'we now have only matching rows visible, so we can
'delete these matching rows
With Cells(2, TestColumn).Resize(cRows - 1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Columns(TestColumn).AutoFilter
End Sub

This should work for you and it is quite quick too.

Marshybid

[figment]

might be a bit slow

Sub test()
Dim rag As Range
Set rag = Range("A:A").Find("Lakeland")
While Not rag Is Nothing
Range(rag.Row & ":" & rag.Row).Delete
Set rag = Range("A:A").FindNext
Wend
Set rag = Range("A:A").Find("Stratford")
While Not rag Is Nothing
Range(rag.Row & ":" & rag.Row).Delete
Set rag = Range("A:A").FindNext
Wend
End Sub

[mikerickson]

What errors does my code get?
Does the actual sheet name match the name in the code?