Menu
Ask a question Report

Conditionally deleting rows in excel 2007 [Solved/Closed]

- - Latest reply: aquarelle
Posts
7241
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
November 22, 2019
 - Jun 20, 2011 at 04:15 PM
Excel Ask a question Report
Hello,

I'm hoping someone here might be able to help me with this. I have a spreadsheet where column A is a project name then columns B, C and D are financial data for the specific project. What I would like to do is delete the entire row for the project only if columns B, C and D ALL contain Zero's.

I can set up a filter and filter out zero's for 1 specific column, but in many of the rows only 1 or 2 of the 3 columns are zero's so that does not work.

Any help is appreciated, thanks.

D
See more 

2 replies

Best answer
Report
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
 305
1
Thank you
Hello

Just a asuggestion
In a new column add the three columns and filter for 0

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 5624 users have said thank you to us this month

Report
Posts
7241
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
November 22, 2019
 488
0
Thank you
Hello,

Try with this macro (I presumed that the first row contained the titles). 

Public Sub DELETE_Rows_CellZero_Col_B_C_D()
Dim x As Long
Dim y As Long
x = Range("C65536").End(xlUp).Row

For y = x To 2 Step -1

    If Cells(y, 2).Value = 0 And Cells(y, 3).Value = 0 And Cells(y, 4).Value = 0 Then
    
        Rows(y).Delete
    
    End If

Next y
End Sub


Best regards
Report
-
thanks you!
Report
-
Hi Aquarelle,

tried the above macro and it worked a treat, but do you have any idea how i can appliy the above to multiple worksheets, for exam name users and customers??

thanks in advance.
Report
aquarelle
Posts
7241
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
November 22, 2019
 488 -
Hi confused,

Please could you load a brief example of your excel file (without any private or personal data) on http://www.speedyshare.com/ then copy past the created link to your next answer.

Best regards
Ask a question