Find specific value, loop through rows and delete rows.

Sneaky Pete

New Member
Joined
Jun 3, 2016
Messages
23
Hey All,

Hope you can help me with this code.
When a user clicks a button, the routine is looking for the second highest value.
In the next step the range is selected, color is set from red tot black, no strikethrough and cells are no longer locked.
This works fine, the routine starts from cell 42 en checks all to the lastrow.

In the same routine I want to delete the rows with the highest value.
Instead of loop through the rows, starting from cell 42 to the lastrow, only the rows of the first Highest value is deleted, the rows below are skipped.

I can't figure it out why, so hope someone can help.
Below is my coee
For Each vcell In Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(42, 3), Worksheets("Sheet1").Cells(Lastrow, 3))
If vcell.Value = SecHighest Then
Set Rng = vcell.Offset(0, -1).Resize(2, 29)
Rng.Font.Color = vbBlack
Rng.Font.Strikethrough = False

If Rng.EntireRow.Group = True Then
Rng.EntireRow.ClearOutline
Else
End If
Rng.Locked = False
End If
Next vcell
End If

'Delete row
For Each vcell In Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(42, 3), Worksheets("Sheet1").Cells(Lastrow, 3))
If vcell.Value = Highest Then
Set Rng = vcell.Offset(0, -1).Resize(2, 29)
Rng.EntireRow.Delete
End If
Next vcell
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top