Compare 2 cell values and delete the row with the smallest value

[KennyA]

Hello everyone. I need some help with a macro that will compare the values of two cells and delete the entire row of the cell with the smallest value.

For example: There are only two opportunities for selecting a blower size based on CFM output.
When Blower #1 is selected a CFM monitor is automatically selected and added to Sheet1.Range("A9:E9").
When Blower #2 is selected a CFM monitor is automatically selected and added to Sheet1.Range("A11:E11").

I want to keep the row that has the largest value in either A9 or A11 and delete the row that has the smallest value.

This is being done since both blowers share the same supply ducting and only one CFM monitor is needed.

Any help on this is appreciated.

Kenny

 

[ImranMalek]

Hi Kenny,
I'm not sure how you are fixing the cells A9 and A11 for comparison however with little modification you can try the code below

Sub del()


If Range("A9").Value > Range("A13").Value Then


    Range("A13").EntireRow.Delete


Else
    
    Range("A9").EntireRow.Delete


End If


End Sub

 

[KennyA]

Hi Imran,
Thanks for the reply. That is exactly the code I was going to use. I was wondering if there was a cleaner way of doing it since I am pretty new to the VBA world.

Thanks again

 

[KennyA]

Well that did not work. Does it make a differance if the A9 and A11 are formatted like this: -2 in cell A9 and -5 in cell A11?

 

[UWMmakow]

What's the error?

 

[KennyA]

No error at all. The row with the lowest value does not get deleted.

 

[UWMmakow]

Not having tested it, I would try replacing

Range("A9").EntireRow.Delete

with

Range("A9").Select
Selection.EntireRow.Delete

Do for A13 as well.

 

[KennyA]

Same result sorry to say. It really can't be this difficult. I must be overlooking something.
Here is the actual code.

If Sheets("Sheet1").Range("A9").Value > Sheets("Sheet1").Range("A11").Value Then
Sheets("Sheet1").Range("A9").Select
Selection.EntireRow.Delete

Else
Sheets("Sheet1").Range("A11").Select
Selection.EntireRow.Delete
Exit Sub
End If

 

[ImranMalek]

Hi Kenny,
The code works fine , it appears I had typo !
Instead of A13 you need to use A11.
P.S : There's no need to select the row before deleting that's an extra step I believe .
Hope it fixes the problem !

 

[ImranMalek]

You are deleting the row which has highest value i.e. when A9 is greater you should delete A11 and not the other way around !