Excel VBA - Delete entire row if all values are 0
 

Hello,
I am new to VBA Programming. Here is what I am trying to do. I have
spreadsheet and I need to go through the spreadsheet columns J throug
W and if the cell values are all zeros then delete the entire row i
any one of the cells have a value of 1 then go to the next row.

Thank for any help you can provide.

Ton

--
Message posted from http://www.ExcelForum.com

Excel VBA - Delete entire row if all values are 0
 

Hi Tony

Try this. It does assume that the data is in rows 2
downwards.


Tony

Sub aaa()
Range("j65536").End(xlUp).Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.Max(Range(ActiveCell,
ActiveCell.Offset(0, 13))) = 0 And WorksheetFunction.Min
(Range(ActiveCell, ActiveCell.Offset(0, 13))) = 0 Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Wend

End Sub

-----Original Message-----
Hello,
I am new to VBA Programming. Here is what I am trying to
do. I have a
spreadsheet and I need to go through the spreadsheet
columns J through
W and if the cell values are all zeros then delete the
entire row if
any one of the cells have a value of 1 then go to the
next row.

Thank for any help you can provide.

Tony


---
Message posted from http://www.ExcelForum.com/

.

Excel VBA - Delete entire row if all values are 0
 

Hi Tony

The following code will do what you want - it runs down the spreadsheet
until it finds a blank cell in Col A and deletes any rows where the total in
J to W is 0.

HTH

David

##########################################

Sub macro1()
Dim x As Integer

x = 1

While (Range("A" & x).Value < "")
If (Application.WorksheetFunction.Sum(Range("J" & x & ":W" & x)) =
0) Then Rows(x & ":" & x).Delete: x = x - 1
x = x + 1
Wend
End Sub

##########################################

"afurlani " wrote in message
...
Hello,
I am new to VBA Programming. Here is what I am trying to do. I have a
spreadsheet and I need to go through the spreadsheet columns J through
W and if the cell values are all zeros then delete the entire row if
any one of the cells have a value of 1 then go to the next row.

Thank for any help you can provide.

Tony


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete entire row if all values are 0
 

I have tried the below code and it deletes every row? I think this coul
be becasue the cells format is text not numeric? Do you think this i
what is happening because all rows get deleted.
Thanks for your help
Tony


Sub macro1()
Dim x As Integer

x = 1

While (Range("A" & x).Value < "")
If (Application.WorksheetFunction.Sum(Range("J" & x & ":W" & x)) =
0) Then Rows(x & ":" & x).Delete: x = x - 1
x = x + 1
Wend
End Su

--
Message posted from http://www.ExcelForum.com

Excel VBA - Delete entire row if all values are 0
 

I changed the format to numeric and it still deletes every row in the
spreadsheet. Please help.
Thanks
Tony


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete entire row if all values are 0
 

Try this one

See this page also
http://www.rondebruin.nl/delete.htm

Sub Example1()
Dim Lrow As Long
Dim CalcMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = .UsedRange.Rows.Count To 1 Step -1
If Application.Sum(Range(.Cells(Lrow, "J"), .Cells(Lrow, "W"))) = 0 Then .Rows(Lrow).Delete
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"afurlani " wrote in message ...
I changed the format to numeric and it still deletes every row in the
spreadsheet. Please help.
Thanks
Tony


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete entire row if all values are 0
 

Hi Tony

Can't get it to fail for me....

Comment out the if line (put a ' at the start) and substitute this line:

msgbox(Application.WorksheetFunction.Sum(Range("J" & x & ":W" & x))

This will display a box for each line showing the total and thus let you
know what's happening - it could Excel is rounding or something similar...

Regards


David


"afurlani " wrote in message
...
I have tried the below code and it deletes every row? I think this could
be becasue the cells format is text not numeric? Do you think this is
what is happening because all rows get deleted.
Thanks for your help
Tony


Sub macro1()
Dim x As Integer

x = 1

While (Range("A" & x).Value < "")
If (Application.WorksheetFunction.Sum(Range("J" & x & ":W" & x)) =
0) Then Rows(x & ":" & x).Delete: x = x - 1
x = x + 1
Wend
End Sub


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete entire row if all values are 0
 

The code works great! Thanks. The problem is I have a macro the
imports a txt file into excell and I can change the cell format to
number and it still does not recognize it has being a number. If I
clear the cell and input a 0 or 1 it then works. It is really weird.
The spreadsheet still has a .txt extension on it because I have not
saved it to xls format. So I saved it to a XLS format and the fields
still are not recognized as number.

Thanks,
Tony


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete entire row if all values are 0
 

Hi Tony

If you mail me the txt file I'll have a play and see if I can figure out
what's going on for you:

david at digicon dot com dot au

Regards

David



"afurlani " wrote in message
...
The code works great! Thanks. The problem is I have a macro the
imports a txt file into excell and I can change the cell format to
number and it still does not recognize it has being a number. If I
clear the cell and input a 0 or 1 it then works. It is really weird.
The spreadsheet still has a .txt extension on it because I have not
saved it to xls format. So I saved it to a XLS format and the fields
still are not recognized as number.

Thanks,
Tony


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete entire row if all values are 0
 

I sent you both files. Thanks for taking a look at this!

Tony


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Delete entire row if all values are 0
 

Dave,
Thank you soooo much for all your help! You have taught me alot about
VBA programming. This is great!

Thanks Again!!!

Ton

--
Message posted from http://www.ExcelForum.com

Excel VBA - Delete entire row if all values are 0
 

Hi Tony

No problem - glad it all worked out for you and thanks for the feedback.

Regards

David

"afurlani " wrote in message
...
Dave,
Thank you soooo much for all your help! You have taught me alot about
VBA programming. This is great!

Thanks Again!!!

Tony


---
Message posted from http://www.ExcelForum.com/