Deleting Rows if..

[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

 

[Ron de Bruin]

Try the example on this page Dennis
http://www.rondebruin.nl/delete.htm

Post back if you need help

 

[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

 

[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)