Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete certain rows (XP)

    I'm not sure how to title this post. If a moderator wants to edit to make it "fit" better, they have my permission.

    What I have is a large spreadsheet containing hundreds of rows of data. Each row contains the following information (I'm not including the irrelevant info):

    Ticket Number
    Start Date
    End Date

    Some information regarding the data:
    Sometimes there is only one row per ticket number.
    Sometimes there are multiple rows for each ticket number, but the dates are the same.
    Sometimes there are multiple rows per ticket number, and multiple dates.

    What I need is a way to delete the unnecessary rows, but keep the relevant information.

    Some examples:

    If ticket 123456 only has one row, nothing else needs to be done. Keep existing row.
    If ticket 234567 has multiple rows, but the same date, I need every row but one removed.
    If ticket 345678 has multiple rows, and multiple dates, I need the earliest start date and the latest end date saved in the same row, and the other rows removed.

    I am attaching an example spreadsheet.

    Thanks in advance for any suggestions.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete certain rows (XP)

    There may be more elegant ways to do it, but here is a macro to do what you want:

    Sub RemoveExtraRows()
    Const lngTicketCol = 2
    Const lngStartCol = 10
    Const lngStopCol = 11
    Dim lngRow As Long
    Dim lngTempRow As Long
    Dim lngMaxRow As Long
    Dim datStart As Date
    Dim datStop As Date

    lngMaxRow = Cells(65536, lngTicketCol).End(xlUp).Row
    For lngRow = lngMaxRow To 2 Step -1
    lngTempRow = lngRow
    Do While Cells(lngRow, lngTicketCol) = Cells(lngTempRow - 1, lngTicketCol)
    lngTempRow = lngTempRow - 1
    Loop
    If lngTempRow < lngRow Then
    datStart = Application.WorksheetFunction.Min(Range(Cells(lngT empRow, lngStartCol), Cells(lngRow, lngStartCol)))
    datStop = Application.WorksheetFunction.Max(Range(Cells(lngT empRow, lngStopCol), Cells(lngRow, lngStopCol)))
    Rows((lngTempRow + 1) & ":" & lngRow).Delete
    Cells(lngTempRow, lngStartCol) = datStart
    Cells(lngTempRow, lngStopCol) = datStop
    lngRow = lngTempRow
    End If
    Next lngRow
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete certain rows (XP)

    This works perfectly! Thanks a bunch.

Posting Permissions

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