delete rows between two specified values

[chopperfoul]

I need a hint or two to help me write a macro to do the following:

To find the first two cells within the same column which contain a specified value and then deleting all the rows between the two values.

Example: say the specified value is contained in both R17C3 and R24C3 -- therefore I'd like to write a macro that would (1) find those two cells, and (2) delete Rows 18 through 23.

Thanks!

 

[Legacy 98055]

Hi Chop...
To recieve a decent code example, provide a few more details....
Are these two values identical?
If so or not, do these values vary or remain the same?
If they vary, you will need to incorporate some type or user input or code to figure the values, if possible...
Is column C the only column which will be searched?
What is the maximum extent of your search range?
Thanks,
Tom

 

[chopperfoul]

Thanks for your response, Tom. Here are a few more details:

(a) No, the two values are not identical
(b) The two values remain the same (are not variable)
(c) Yes, only one column will be searched
(d) Only the top 500 rows of a worksheet need be searched

I would also add the following detail: each of the two values appears exactly 14 times within the column, and in alternating order. Therefore, after the 14th set of rows is deleted, I would terminate the macro.

Thanks.

 

[Corni]

The assumption for the code below is that the control values show up in column C 14 times alternating, starting with Value1:

Dim Value1 As Integer, Value2 As Integer
Dim rowNumber1 As Integer, temp As Integer, rowNumber2 As Integer
Dim delRows As Integer, leftRows As Integer
Dim i As Integer

leftRows = 500
rowNumber1 = -1

Value1 = InputBox("Please enter the first control value")
Value2 = InputBox("Please enter the second control value")

For i = 1 To 14
temp = WorksheetFunction.Match(Value1, Range(Cells(rowNumber1 + 2, 3), _
Cells(leftRows, 3)), 0) + rowNumber1 + 1
rowNumber2 = WorksheetFunction.Match(Value2, Range(Cells(rowNumber1 + 2, 3), _
Cells(leftRows, 3)), 0) + rowNumber1 + 1
rowNumber1 = temp

Range(Cells(rowNumber1 + 1, 3), Cells(rowNumber2 - 1, 3)).Select
Selection.EntireRow.Delete
Cells(rowNumber1 + 2, 3).Select
delRows = rowNumber2 - rowNumber1 - 1
leftRows = leftRows - delRows
Next i