Log in

View Full Version : Delete Rows if Value in Column A



ssinghal
07-10-2008, 05:33 AM
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
07-10-2008, 06:11 AM
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
07-10-2008, 06:18 AM
that doesnt work-get errors

marshybid
07-10-2008, 08:34 AM
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
07-10-2008, 08:40 AM
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
07-10-2008, 12:15 PM
What errors does my code get?
Does the actual sheet name match the name in the code?