Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting rows based on column values Dazed and Confused[_2_] New Users to Excel 3 February 6th 09 11:47 PM
Deleting Rows based on Column Critieria blackmanofsteel40 Excel Discussion (Misc queries) 1 September 7th 07 09:05 PM
Deleting rows based on values in a column Bob Excel Programming 0 June 13th 07 10:45 PM
Deleting rows based on values in a column Bernie Deitrick Excel Programming 1 June 13th 07 10:43 PM
Deleting rows based upon the value in column D Sean Excel Programming 2 October 25th 04 08:59 PM


All times are GMT +1. The time now is 10:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"