Excel VBA - Delete entire row if all values are 0

[afurlani]

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

 

[acw]

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

 

[David Coleman]

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]

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

 

[afurlani]

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

 

[Ron de Bruin]

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

 

[David Coleman]

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]

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

 

[David Coleman]

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]

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

Tony

 

[David Coleman]

Hi Tony

Just had a quick look and I've three comments:

1) Column A does not contain data in each and every cell so the loop needs
to be changed to show

While (Range("B" & x).Value <> "")

2) With the text document you sent me and your macro I end up with no data
in J thru W on any row - therefore the sum of each J to W = 0, therefore it
deletes all the rows. I stuck some random numbers in some random cells and
it didn't delete those rows - only totals of 0 led to rows being deleted.

3) The headings get deleted as the macro starts checking at row 1 - if this
shouldn't be, set x = 3 rather than x = 1

If this doesn't help enough, can you let me have some data that sticks
entries in J - W and we'll see what happens.....

Regards

David

 

[David Coleman]

One further thought - do you need it to delete if the total is 0 or if all
the cells are 0?

Excel treats empty cells as having a value of 0 so no data = 0 =
deletion....

 

[afurlani]

If you open the macro.xls, and make sure the path is to the TX
document, click on the button that says click Me, it brings the TX
file into excel and there is data in columns J - W there is either a
or 1. If the totals of J-W =0 then delete entire row. I am not sur
why you are saying that you are not getting any data in J - W.

Thanks for all your help on this!

Ton

 

[David Coleman]

Hi Tony

That copy worked much better - I had no 0s or 1s in any J-W column in the
first version.....

The issue is how the information is getting added to the spreadsheet - Excel
is storing all the numbers as text. You'd think that you can tell Excel
that they are numbers by doing "Format, Cell, Number" but it doesn't
actually convert it.

However, add the following code after the 'new code here comment in your
macro and it all works fine. The first bit forces the conversion by
multiplying each text value as 1 - Excel says "can't do this as text, so
I'll convert it automatically" and then my code works fine....

I'll mail you my version with these changes already made.

Kind regards

David



'Convert numbers stored as text to actual numbers
Range("G1").Value = 1
Range("G1").Select
Selection.Copy
Range("I3:W205").Select 'Watch the range that's selected and
converted?!?!?
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("G1").Value = ""

'now delete the rows that need to be deleted
x = 1
While (Range("B" & x).Value <> "")
If (Application.WorksheetFunction.Sum(Range("J" & x & ":W" & x)) =
0) Then Rows(x & ":" & x).Delete: x = x - 1
x = x + 1
Wend

 

[afurlani]

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

Thanks Again!!!

Ton

 

[David Coleman]

Hi Tony

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

Regards

David