Log In
Register
Home
Active Member
June 25, 2016
Offline
Hi:
I have a worksheet where I need to remove all rows if certain cells have a value of zero. Is there a code that will do this easily?
Thanks in advance.
C.Baker
VIP
Trusted Members
June 25, 2016
Offline
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
Active Member
June 25, 2016
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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.
Sub DeleteRow()
Dim r As Long
Dim FirstRow As Long
Dim LastRow As Long
'Need to avoid the top blank rows and grand total
FirstRow = 7
LastRow = Cells(Rows.Count, "BG").End(xlUp).Row - 1
For r = LastRow To FirstRow Step -1
If Cells(r, "BD") = 0 And Cells(r, "BG") = 0 Then
Cells(r, "BI") = "Delete" 'remove if code OK
'Rows(r).Delete 'use this if code OK
End If
Next r
End Sub
Hope this helps
Sunny
Active Member
June 25, 2016
Offline
Hi Sunny:
I am just getting around to trying the code. It worked perfectly!! Thank you so much!
CBaker
New Member
October 23, 2017
Offline
Hi I would like to automatically delete the rows with zeros (the bottom 3)in the data table like below...
| X | Y | Z | HOLE ID | PLANNED TIMING | EDD ID |
| 4868.1 | 10551.97 | 1335.39 | O40 | 1439 | #N/A |
| 4871.41 | 10548.92 | 1335.54 | O41 | 1492 | #N/A |
| 4874.75 | 10545.91 | 1335.65 | O42 | 1545 | #N/A |
| 0 | 0 | 0 | 0 | 0 | 9361 |
| 0 | 0 | 0 | 0 | 0 | 9361 |
| 0 | 0 | 0 | 0 | 0 | 9361 |
This data is generated by a macro. Any advise please!
VIP
Trusted Members
June 25, 2016
Offline
Hi Kasonde
If your data starts from A1, then the codes from my post #2 above should work for you.
Good luck.
Sunny
January 17, 2020
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
January 17, 2020
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
January 17, 2020
Offline
Thank you Sunny.
That works brilliantly
January 17, 2020
Offline
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
October 5, 2010
Offline
Hi Sean,
No workbook is attached.
Regards
Phil
January 17, 2020
Offline
My apologises there doesn't seem to be the workbook attached
Trusted Members
December 20, 2019
Offline
it looks like you have the worksheets protected so the macro cant delete anything.
you can get the macro to unprotect the work sheet by adding activesheet.unprotect "summerscales" to the beginning of the macro (just after 'Sub DeleteRow()') and then turning it back on with activesheet.protect "summerscales" just before exit sub
January 17, 2020
Offline
Thanks for your reply Purfleet.
I have the code working correctly as a standalone bit of code. My problem is I need it to run within my code that's already there in the 'Inventory Booked Out' sheet.
When I insert it into there I then experience problems with it.
Trusted Members
December 20, 2019
Offline
How are you putting the DeleteRow Sub in the existing ? are you pasting the whole code into a certain part of your existing code??
I noticed that you had the DeleteRow Sub code in module5, so i added a call DeleteRow at LN11 in the code on Sheet4 and apart from Inventory Booked in being protected seem to run okay (not checked output)
Private Sub Worksheet_Change(ByVal Target As Range)
'Unprotect Workbook
Sheets("Inventory Booked Out").Unprotect Password:="summerscales"
Sheets("Inventory Booked In").Unprotect Password:="summerscales"
Sheets("Scans with Date Time Stamp").Unprotect Password:="summerscales"
Sheets("Inventory List").Unprotect Password:="summerscales"
Call DeleteRow
Dim Item As String
Dim strDscrpt As String
Dim strPrice As String
Dim SearchRange As Range
Dim rFound As Range
Dim strBC As String
January 17, 2020
Offline
Thanks Purfleet for your reply and pointing out the obvious to me that I had stupidly missed.
I didn't have 'Inventory Booked In' unprotected. Once I had included that all worked fine in how I already had it set to call DeleteRow.
Trusted Members
December 20, 2019
Offline
Hi Sean
Glad it is working - i have found with vba that it is often the most simple things that cause the code to break - i once spent an hour trying to get xlcentre to work only to realise that vba needs the US spelling of Centre, changed to xlcenter and it worked.
Still kicking myself for that one!
Purfleet
1 Guest(s)
