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
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
[VBA]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[/VBA]
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 removeOriginally Posted by ssinghal
[vba]
'-----------------------------------------------------------------
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
[/vba]
This should work for you and it is quite quick too.
Marshybid
might be a bit slow
[VBA]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[/VBA]
What errors does my code get?
Does the actual sheet name match the name in the code?