Problems with Deleting rows using VBA

[Jeff]

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(i5:p5)),0,SUM(I5:p5))"
'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(i5:p5)),0,SUM(I5:p5))"
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

--
Regards,
Tom Ogilvy


"Jeff" <[email protected]> wrote in message
news:[email protected]...
> 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(i5:p5)),0,SUM(I5:p5))"
> '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(i5:p5)),0,SUM(I5:p5))"
> 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

[Helen]

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.

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Jeff" <[email protected]> wrote in message
> news:[email protected]...
> > 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(i5:p5)),0,SUM(I5:p5))"
> > '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(i5:p5)),0,SUM(I5:p5))"
> > 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
>
>
>