Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: delete rows between two specified values

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •