Problems with Deleting rows using VBA

[Guest]

I amm going nuts!!!!! I have some code I've worked on and think it should
delete an entire row of data if column "Q" is equal to zero. But everytime I
run the code it deletes everything!!!

Here is my code:
ShtRef = "CPCT " & Xlmonth2
Frmla = "=IF(ISERROR(sum(i55)),0,SUM(I55))"
'z = xlApp.Workbooks(DartRef).Worksheets(ShtRef).UsedRange.Rows.Select
xlApp.Application.DisplayAlerts = False
xlApp.Workbooks(DartRef).Worksheets(ShtRef).Range(all).RemoveSubtotal
xlApp.Application.DisplayAlerts = False

z = xlApp.Workbooks(DartrEF).Worksheets(ShtRef).UsedRange.Rows.Count
Plan_Months = xlApp.Range(xlApp.Cells(1, 17), xlApp.Cells(z, 17))
RowNdx = z
ShtRef = "CPCT " & Xlmonth2
Frmla = "=IF(ISERROR(sum(i55)),0,SUM(I55))"
xlApp.Workbooks(DartRef).Worksheets(ShtRef).Range("i:q").Delete
xlApp.Workbooks(DartRef).Worksheets(ShtRef).Range("Q:CH").Delete
xlApp.Workbooks(DartRef).Worksheets(ShtRef).Range("Q4:" & "Q" & z) = Frmla
xlApp.Workbooks(DartRef).Worksheets(ShtRef).Range("Q4:" & "Q" & z).Select
Selection.Copy
xlApp.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "0.00"

For RowNdx = RowNdx To 4 Step -1
If xlApp.Workbooks(DartRef).Worksheets(ShtRef).Cells(RowNdx, 17).Value =
"0" Then
xlApp.Workbooks(DartRef).Worksheets(ShtRef).Cells(RowNdx,
17).EntireRow.Delete
End If
Next RowNdx

aNY hELP IS GREATLY APPRECIATED

 

[Tom Ogilvy]

Hello Helen,

Using RowNdx to perform two different purposes seems odd. Try using two
different variables.

For i = RowNdx To 4 Step -1
If xlApp.Workbooks(DartRef).Worksheets(ShtRef) _
.Cells(i, 17).Value = "0" Then
xlApp.Workbooks(DartRef).Worksheets(ShtRef) _
.Cells(i, 17).EntireRow.Delete
End If
Next i

 

[Guest]

I tried your suggedstion but allas it still didn't work. I ended up having to
to copy and paste special values only, converting the equations to constants
before I could get my code to work.