Register
Log In
Unanswered
Home
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
Dashboards
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
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
Dashboards
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
Most Users Ever Online: 57
Currently Online: Meyanui
16 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
