1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Macro to sort data by date, then move to new cell

Discussion in 'Business Applications' started by c_malley, Feb 3, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. c_malley

    c_malley Thread Starter

    Joined:
    May 29, 2003
    Messages:
    2
    Hoping to get some help on this question please:). I have reviewed the forum and found a similar post, but the sample data is more predictable than mine and the Macro posted doesn't work on my data. See post "Excel Macro to Move data in Rows to column" 28-Aug-2008, 05:47 AM".

    On my data, I would like to be able to take the yellow row, and move the data up to the previous row starting in column "R". The rows I would like on the same line, all begin with a date. I don't know how to make a looping Macro that will identify the "date lines", and take the "second date line", and move it to the "first date line's" row.

    Any help would be appreciated.
    I have attached some sample data. (I hope that's okay?!)

    Thank you!!!!
    Christine

    PS - The range on data varies.
     

    Attached Files:

  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Christine,

    this macro below works well with the sample data.

    Code:
    Sub sorting()
        Dim Rng As Range, c As Range, i As Long
        Set Rng = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
        For Each c In Rng
            i = (i + 1) Mod 2
            If i = 0 Then
                With c.Resize(, 15)
                    .Copy .Offset(-1, 17)
                    .ClearContents
                End With
            End If
        Next
    End Sub
    
    How it works:
    1. Takes all those cells in column A of the active sheet that contain numbers. (Dates are actually numbers, only formatted to look like dates.)
    2. In a loop, it takes every second cell of the set of cells selected in Step #1.
    3. Takes a 15 cells wide region starting with the cell selected in Step #2, and copies it to the region starting with the cell positioned one row up and 17 columns to the right.
    4. Deletes the content of the original 15 cells wide region.

    Caution:
    1. If there are any numbers in column A besides dates, then the algorithm is wrong and the macro will fail.
    2. If the dataset in the second date line is wider than 15 cells wide then the rest will remain in place. But the macro can be easily updated, just change the Resize line.
    3. Don't run it twice on the same worksheet.
    4. Always make a backup copy of your data before running the macro, because it is not Undo-able.

    Jimmy
     
  3. c_malley

    c_malley Thread Starter

    Joined:
    May 29, 2003
    Messages:
    2
    Jimmy the Hand,

    You are a Wizard!!! The Macro you wrote is like poetry.

    Thank you so much for your help. You've saved me hours of work!

    Sincerely, Christine
     
  4. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Christine,

    thanks for the praise. You are of course welcome, and I'm glad it worked out for you.
    I suppose you may want to return with further problems like removing useless data and empty rows, sorting the data automatically, etc. But if not, if all your dreams are fulfilled (;)), then please mark the thread as "solved".

    Jimmy
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/900057

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice