Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows from a structured table based on the value of a column
I'm using the code below to delete rows from a structured table based on a column having a zero value. In the table I have there are 6 rows with a zero value in the "Total" column.
The code deletes four of the rows but doesn't delete the first 2 rows that have a zero in the "Total" column. For Each tblRow In [OrdersTable].Rows If (tblRow.Columns(tblRow.ListObject.ListColumns("Tot al").Index).Value) = "0" Then tblRow.Delete Shift:=xlUp End If Next Anyone have an idea why this code would skip deleting 2 of the rows where the "Total" column is 0? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows from a structured table based on the value of a column
I'm using the code below to delete rows from a structured table based
on a column having a zero value. In the table I have there are 6 rows with a zero value in the "Total" column. The code deletes four of the rows but doesn't delete the first 2 rows that have a zero in the "Total" column. For Each tblRow In [OrdersTable].Rows If (tblRow.Columns(tblRow.ListObject.ListColumns("Tot al").Index).Value) = "0" Then tblRow.Delete Shift:=xlUp End If Next Anyone have an idea why this code would skip deleting 2 of the rows where the "Total" column is 0? Thanks When deleting rows via a loop, you must start at the last row and work up so your code doesn't skip over shifted rows when moving to 'Next' in the loop. So if the 1st 4 rows are to be deleted, deleting row1 puts row2 in its position and the 'Next' row to get deleted is row3 because it shifted into row2's position, ..and so on. The result is the original data from Rows 2,4,5,6 where rows 1-4 used to be. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows from a structured table based on the value of a column
On Tuesday, March 29, 2016 at 12:29:24 PM UTC-5, GS wrote:
I'm using the code below to delete rows from a structured table based on a column having a zero value. In the table I have there are 6 rows with a zero value in the "Total" column. The code deletes four of the rows but doesn't delete the first 2 rows that have a zero in the "Total" column. For Each tblRow In [OrdersTable].Rows If (tblRow.Columns(tblRow.ListObject.ListColumns("Tot al").Index).Value) = "0" Then tblRow.Delete Shift:=xlUp End If Next Anyone have an idea why this code would skip deleting 2 of the rows where the "Total" column is 0? Thanks When deleting rows via a loop, you must start at the last row and work up so your code doesn't skip over shifted rows when moving to 'Next' in the loop. So if the 1st 4 rows are to be deleted, deleting row1 puts row2 in its position and the 'Next' row to get deleted is row3 because it shifted into row2's position, ..and so on. The result is the original data from Rows 2,4,5,6 where rows 1-4 used to be. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Thanks, that's what I figured after looking at a few examples on the web. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows based on column values | New Users to Excel | |||
Deleting Rows based on Column Critieria | Excel Discussion (Misc queries) | |||
Deleting rows based on values in a column | Excel Programming | |||
Deleting rows based on values in a column | Excel Programming | |||
Deleting rows based upon the value in column D | Excel Programming |