Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to move information and delete rows (2002)

    In the attached sample, rows A2 to A7 are the desired setup which was done manually. What I would like is a macro or some other method to unmerge those rows with dates(example row 8), placing the date in column A, and the corresponding row. Then delete the contents the shaded rows (except row A) and move the subsequent rows up. The number of rows between the dates varies as more teams were added through out the years.
    Not sure that the explanation is all that good. Hopefully the attached shows what is needed.

    The actual data is greater than 1000 rows.

    Thanks.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to move information and delete rows (2002)

    You can do it relatively quickly manually with autofilter and a formula (do this on a copy in case I do not understand)

    Select columns B:I then Format - cells- Alignment (tab), uncheck "Merge cells". [This will unmerge the cells)

    In A8 enter:
    =IF(B7="Team",B6,IF(OR(B8="Team",B9="Team"),"T",NA ()))
    Copy/ Autofill down the column. [This puts dates in A in first row with the team, "T" in shaded and date rows (to be deleted), and na() in all others]
    Convert the formulas to values:
    Select Column A
    Edit - Copy
    Edit - paste special, select values <ok>

    Delete the rows:
    Select A1. Then Data - filter - autofilter
    In the pulldown in A1 select the "T"
    Select all the rows
    Edit - Delete row

    Delete "na" in the "blank rows"
    In the pulldown in A1 select the #N/A
    Select all the cells in Col A, but the header and press <del>

    Data - filter - autofilter will remove the filter and be what you want.

    Steve

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

    Re: Macro to move information and delete rows (2002)

    If you still want a macro:

    Sub MoveEmUp()
    Dim m As Long
    Dim r As Long
    Dim strYear As String
    Application.ScreenUpdating = False
    m = Range("B65536").End(xlUp).Row
    For r = m To 2 Step -1
    If Not (Range("B" & r).MergeArea.Address = Range("B" & r).Address) Then
    strYear = Range("B" & r)
    Range("A" & r & ":A" & (r + 1)).EntireRow.Delete
    Range("A" & r) = strYear
    Range("A" & r).Font.Bold = True
    End If
    Next r
    Application.ScreenUpdating = True
    End Sub

  4. #4
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to move information and delete rows (2002)

    Thanks. This works well.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to move information and delete rows (2002)

    Thanks. This also works well.

Posting Permissions

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