Find and remove row

  • Thread starter Thread starter TheLearner
  • Start date Start date
T

TheLearner

Hello Everyone,

I have a file that has 4 columns, one of which stores a number value.

When this column has a value of 0 I want to remove the entire row.

Can I do this with Find/Replace or how would I do this?

Regards,
 
Hi,

Right click your sheet tab, view code and paste this in and run it. It
currently works on Column A so change this to suit. You will also need to
change the 1 in the line marked ** to match the column you are working on. 2
represents Column B etc.

Sub delete_Me()
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For x = Lastrow To 1 Step -1
If Cells(x, 1).Value = 0 Then '**
Rows(x).Delete
End If
Next
End Sub

Mike
 
Recorded
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 8/12/2008 by Donald B. Guillett
'

'
Range("A1:A15").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="0"
Range("A13:A14").Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1
End Sub

Cleaned up
Sub delete0rows()
mc = "a"
lr = Cells(rows.Count, mc).End(xlUp).Row
Range("a1:a" & lr).AutoFilter Field:=1, Criteria1:="0"
Range("A13:A1" & lr).EntireRow.Delete
Range("a1:a" & lr).AutoFilter
End Sub
 
Range("A13:A1" & lr).EntireRow.Delete
should be
Range("A1:A" & lr).EntireRow.Delete
Click to expand...
But just use this instead

Sub delete0rows1()
lr = Cells(rows.Count, "a").End(xlUp).Row
With Range("a1:a" & lr)
.AutoFilter Field:=1, Criteria1:="0"
.EntireRow.Delete
.AutoFilter
End With
End Sub
 
You could do it with Edit>Find.

Select the column and Edit>Find

What: 0

Find All

Now in the "Found" dialog box hold SHIFT key and scroll to bottom and select
last "found" cell.

Edit>Delete>Entire Row.


Gord Dibben MS Excel MVP
 
Back
Top