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

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

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.