Macro to delete line

K

Kevin Depree

Hi,

I want to write a macro that looks at a sheet, and any
line found with a '0' in a particular column gets deleted.

I.E If the macro finds a '0' in A5 then it deletes the
whole of the '5' row.


Any ideas
 
T

Tim Coddington

Once you've found the range with the '0'
{range}.EntireRow.Delete
will delete the row. If you have done a select so the '0' is the
current position of the cursor, then ...
activecell.EntireRow.Delete
 
J

John Mansfield

Hi,

Try something like:

Sub DeleteRowsUsingAutoFilter()

Dim lLastRow As Long
Dim Rng As Range
Application.ScreenUpdating = False
Rows(1).Insert
Range("A1").Value = "Temp"
With ActiveSheet
.UsedRange
lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Range("A1", Cells(lLastRow, "A"))
Rng.AutoFilter Field:=1, Criteria1:="0"
Rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.UsedRange
End With
End Sub

John Mansfield
pdbook.com
 
Top