Deleting Rows if..

D

dennis

I'm trying to get visual basic to delete all the rows that
have a certain value (i.e. 1) in a specific column.

I've seen this done with a loop, but I'm not sure how it
all works.


Any insight would be great

Thanks

Dennis
 
F

Frank Kabel

Hi Dennis
try the following macro:

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
with Cells(RowNdx, "A")
if .value = 1 then
Rows(RowNdx).Delete
End If
end with
Next RowNdx
Application.ScreenUpdating = True
End Sub

'--------
this searches for the value '1' in column A and if found deletes the
entire row. Some comments:
- you have to loop backwards. That is starting with the last row going
upwards
- change the column identifier 'A' and the value to look for according
to your needs
 
B

Bob Phillips

Here's a starter

Range("A1").EntireRow.Insert
Columns("A:A").AutoFilter Field:=1, Criteria1:="myText"
Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top