Find and remove row
 

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,

Find and remove row
 

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

"TheLearner" wrote:

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,

Find and remove row
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TheLearner" wrote in message
...
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,

Find and remove row
 

Range("A13:A1" & lr).EntireRow.Delete
should be
Range("A1:A" & lr).EntireRow.Delete
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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TheLearner" wrote in message
...
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,

Find and remove row
 

You could do it with EditFind.

Select the column and EditFind

What: 0

Find All

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

EditDeleteEntire Row.


Gord Dibben MS Excel MVP

On Tue, 12 Aug 2008 03:58:01 -0700, TheLearner
wrote:

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,