Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Problem - Please Help
Can anyone tell me why this is not working? This is suppose to check each row on the sheet and if C * E < 3000 the delete that row. I've rewritten this sub many time but cannot get it t work properly. In advance. Many Thanks!! Sub Filter_Two() Dim valPrice As Currency Dim valQTY As Variant FinalRow = ActiveSheet.Range("A65536").End(xlUp).Row For i = 2 To FinalRow 'If ActiveSheet.Range("A" & i).Text = "" Then Exit Sub valQTY = ActiveSheet.Range("C" & i).Value valPrice = ActiveSheet.Range("E" & i).Value If valPrice * valQTY < 3000 Then Rows(i).Activate Rows(i).Select Rows(i).Delete FinalRow = FinalRow - 1 End If Next i End Su -- mpeplo ----------------------------------------------------------------------- mpeplow's Profile: http://www.excelforum.com/member.php...fo&userid=3481 View this thread: http://www.excelforum.com/showthread.php?threadid=56213 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Problem - Please Help
You need to decrement i if you delete the current row. For Example
Product of Row 2 = <3000 Product of Row 3 = <3000 Your code says delete row 2, now row 3 is row 2 contain product <3000 your code says goto row 3 etc... So insert this line after Rows(i).Delete i = i - 1 HTH Die_Another_Day mpeplow wrote: Can anyone tell me why this is not working? This is suppose to check each row on the sheet and if C * E < 3000 then delete that row. I've rewritten this sub many time but cannot get it to work properly. In advance. Many Thanks!! Sub Filter_Two() Dim valPrice As Currency Dim valQTY As Variant FinalRow = ActiveSheet.Range("A65536").End(xlUp).Row For i = 2 To FinalRow 'If ActiveSheet.Range("A" & i).Text = "" Then Exit Sub valQTY = ActiveSheet.Range("C" & i).Value valPrice = ActiveSheet.Range("E" & i).Value If valPrice * valQTY < 3000 Then Rows(i).Activate Rows(i).Select Rows(i).Delete FinalRow = FinalRow - 1 End If Next i End Sub -- mpeplow ------------------------------------------------------------------------ mpeplow's Profile: http://www.excelforum.com/member.php...o&userid=34812 View this thread: http://www.excelforum.com/showthread...hreadid=562139 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Problem - Please Help
When you are deleting rows you want to work from the bottom up. Otherwise
every delete moves things around on you. For i = FinalRow to 2 Step -1 'Don't change FinalRow -- HTH... Jim Thomlinson "mpeplow" wrote: Can anyone tell me why this is not working? This is suppose to check each row on the sheet and if C * E < 3000 then delete that row. I've rewritten this sub many time but cannot get it to work properly. In advance. Many Thanks!! Sub Filter_Two() Dim valPrice As Currency Dim valQTY As Variant FinalRow = ActiveSheet.Range("A65536").End(xlUp).Row For i = 2 To FinalRow 'If ActiveSheet.Range("A" & i).Text = "" Then Exit Sub valQTY = ActiveSheet.Range("C" & i).Value valPrice = ActiveSheet.Range("E" & i).Value If valPrice * valQTY < 3000 Then Rows(i).Activate Rows(i).Select Rows(i).Delete FinalRow = FinalRow - 1 End If Next i End Sub -- mpeplow ------------------------------------------------------------------------ mpeplow's Profile: http://www.excelforum.com/member.php...o&userid=34812 View this thread: http://www.excelforum.com/showthread...hreadid=562139 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Problem - Please Help
All of this information has been extremly helpful! THANK YOU! -- mpeplo ----------------------------------------------------------------------- mpeplow's Profile: http://www.excelforum.com/member.php...fo&userid=3481 View this thread: http://www.excelforum.com/showthread.php?threadid=56213 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Simple problem | New Users to Excel | |||
Help simple problem | Excel Programming | |||
Simple problem | Excel Discussion (Misc queries) | |||
A simple problem... | Excel Programming |