Hi Baker

Without seeing some sample data, it is difficult to determine where the zero values are. If they are all in a single column then you can give this code a try.

It checks for zero values along column A and delete the entire row if found.

Sub DeleteRow()
Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, 1) = 0 Then
Rows(r).Delete
End If
Next r
End Sub

Sunny

Sunny:

Thank you for taking the time to answer my question.  You stated the code will check for zero values in column A then delete if found.

What about if the cell I need to reference is not in column A.  Actually I have two different columns that I need to reference to see if cell(s) have a zero value but are on the same row.  

Attached is a file that shows what I need.  It has several hidden columns but I have highlighted (yellow) the 2 columns that need to be checked for the zero value.  The first column may have a value in a cell but not in the second column.  I would not want that row deleted.  Only rows that have a zero value in both columns should be deleted.

I may be asking for something that is too complicated.  But I figured I would give it a shot

Thanks

CBaker

Hi Cindy

Your data is a bit more complicated (and dangerous) to delete due to the blank rows on top and hidden columns that also contains data.

As a safety measure, the code below will add text to column BI to see if that row can be deleted. Check to ensure that it is correct.

Once you are sure that the code is OK then you can remove the line in blue and use Rows(r).Delete instead.

Hope this helps

Sunny

Hi Sunny:

I am just getting around to trying the code.  It worked perfectly!!  Thank you so much!

CBaker

Hi I would like to automatically delete the rows with zeros (the bottom 3)in the data table like below...

This data is generated by a macro. Any advise please! 

Hi Kasonde

If your data starts from A1, then the codes from my post #2 above should work for you.

Good luck.

Sunny

Hi Sunny,

Your code in post #2 works brilliantly for deleting the row on the active sheet.

What I am requiring is very similiar to this code.

I have a workbook with 5 worksheets in it. I need the code to search the data in worksheet 'Inventory List' column E.

Where there is a zero I need that row deleting. 

Here comes the bit I cannot figure out.

In worksheets 'Inventory Booked In' and 'Inventory Booked Out' I need the same corresponding data deleting.

In all 3 worksheets Column A contains the same data i.e. A unique code for every entry

Only in 'Inventory List' is the column that contains a quantity.

Hi Sean

Just change the column letter in RED to the column (column E in this example) that you want to check.

Sub DeleteRow()
Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, "E") = 0 Then
Rows(r).Delete
End If
Next r
End Sub

Hope this helps

Sunny

Hi Sunny,

Thanks for your reply.

I knew I had to change If Cells(r, "E") = 0 Then

The bit I cannot figure out is.

In worksheets 'Inventory Booked In' and 'Inventory Booked Out' I need the same corresponding data deleting.

In all 3 worksheets Column A contains the same data i.e. A unique code for every entry

Only in 'Inventory List' is the column that contains a quantity.

I.E. If E5 in 'Inventory List' is a zero the above code would delete row 5

What I need next is the data that is in A5 to be searched for in 'Inventory In' & 'Inventory Out' and then delete the corresponding row

Give this a try

Sub DeleteRow()
Dim r As Long
Dim CodeNo
Dim LastRow As Long
Dim RowNo
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set ws1 = Worksheets("Inventory list")
Set ws2 = Worksheets("Inventory Booked In")
Set ws3 = Worksheets("Inventory Booked Out")

With ws1
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1
If .Cells(r, "E") = 0 Then

'Get the unique code number then delete the required rows
CodeNo = .Cells(r, 1)
.Rows(r).Delete

'Search for code and delete row if found
RowNo = Application.Match(CodeNo, ws2.Range("A:A"), 0)
If Not IsError(RowNo) Then
ws2.Rows(RowNo).Delete
End If

RowNo = Application.Match(CodeNo, ws3.Range("A:A"), 0)
If Not IsError(RowNo) Then
ws3.Rows(RowNo).Delete
End If
End If
Next r
End With
End Sub

Sunny

Thank you Sunny.

That works brilliantly

Hi Sunny,

I tried running the above code on a test sheet.

When I have used it in my workbook I am having problems with it.

I have attached my workbook for you to look at and see how the above code could work in it if you do not mind.

The password for everything on the workbook is summerscales

Hi Sean,

No workbook is attached.

Regards

Phil

My apologises there doesn't seem to be the workbook attached