Deleting Rows in Excel 2000

[graemematthew]

Is there a way of setting a macro that will look at tall the cells in one column and where it finds a specific value it deletes the whole row.

 

[MadForIt]

Hi,

This works but it's not very quick.
This will search through all the cells in column A and will delete the entire row where the cell contains ABC

Sub test()
For Each Cell In Range("A:A&quot
If Cell = "ABC" Then
Cell.EntireRow.Delete
End If
Next
End Sub

 

[MadForIt]

Sorry ignore previous posting. This won't remove all entries as, as each row is deleted, the cell reference goes out of sync.

Try something like this.

Sub test()
Count = Range("A:A&quot.SpecialCells(xlCellTypeLastCell).Row
Do
If Cells(Count, "A&quot = "ABC" Then
Rows(Count).EntireRow.Delete
End If
Count = Count - 1
Loop Until Count = 0
End Sub

 

[MadForIt]

Graeme,

Couldn't leave it as it was as if there is alot of data it could take a while!

As before this will delete all rows where there is ABC in column A.

Sub test()
Set One = Columns("A&quot.Find("ABC", lookat:=xlWhole)
If Not (One) Is Nothing Then
Cells(One.Row, "A&quot.Select
Cells.ColumnDifferences(ActiveCell).EntireRow.Hidden = True
Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
Cells.EntireRow.Hidden = False
End If
End Sub