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

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

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

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