| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
| Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
| Deleting cells from chart source data, causing problems | Excel Discussion (Misc queries) | |||
| deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions | |||
| Problems deleting custom menu = double entries | Excel Programming | |||
Linear Mode
