|
Problems with Deleting rows using VBA
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).UsedRa nge.Rows.Select xlApp.Application.DisplayAlerts = False xlApp.Workbooks(DartRef).Worksheets(ShtRef).Range( all).RemoveSubtotal xlApp.Application.DisplayAlerts = False z = xlApp.Workbooks(DartrEF).Worksheets(ShtRef).UsedRa nge.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 |
Problems with Deleting rows using VBA
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" wrote in message ... 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).UsedRa nge.Rows.Select xlApp.Application.DisplayAlerts = False xlApp.Workbooks(DartRef).Worksheets(ShtRef).Range( all).RemoveSubtotal xlApp.Application.DisplayAlerts = False z = xlApp.Workbooks(DartrEF).Worksheets(ShtRef).UsedRa nge.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 |
Problems with Deleting rows using VBA
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" wrote in message ... 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).UsedRa nge.Rows.Select xlApp.Application.DisplayAlerts = False xlApp.Workbooks(DartRef).Worksheets(ShtRef).Range( all).RemoveSubtotal xlApp.Application.DisplayAlerts = False z = xlApp.Workbooks(DartrEF).Worksheets(ShtRef).UsedRa nge.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 |
| All times are GMT +1. The time now is 05:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com