Macro to move rows in excel automatically

[Closed]
Report
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
-
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
-
Hello,

I need to create a macro for Excel2010 that moves one to two rows of information to a preceding row and adds each row as a set of new columns to the preceding row.

Example data is here: https://docs.google.com/spreadsheet/ccc?key=0AppTn7t0jb48dHUtWXNnOUVfRXhqbXQ0WldSbTUwV3c

For example, rows 3 and 4 need to both be moved to row 1, and both need to be moved to after column R. I want to move every column in row 3 and 4, except column A. Additionally, empty cells need to be maintained through the end of titled columns. Then, row 6 needs to be moved to row 5 also after column R. Additionally, the newly empty rows need to be deleted.

Thanks for the help!


9 replies

Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
1
I have not changed the data at all. Row 1 has all the headers. The order of the headers has not been changed. Data starts in row 2.

What else could be causing the problem?
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
is there any logic for these transfers. give one example
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
1
The logic is that I am using a database program that automatically creates a new row for each event (labeled in column A) for the same study ID. But in order to compare the data between study IDs using statistical software, all events must be in the same row.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
As you are changing the original data FIRST copy original data in SHEET1 TO SHEET 2 so that original data is preserved for future use

Your original data is from A1 to Q13 IN SHEET1 AND SHEET 2
One suggestion for the future:
If you are thinking of manipulating data by using a macro better do not leave any cell blank, in which case it is easy to write the macro
Any how do not worry I have incorporated some code statements in the macro to fill up blank cells with some random string "X"
You just copy the macro in the module. I am sure you know how to do it.

Open vb editor (alt +function key F11)
Hit control +R and you see all the open excel files. Highlight this relevant file and click insert (menu) -module
A module window opens and in that copy paste the macro and save the file (remember after copying original data in SHEET 2.
Now run the macro
The macro is

Sub test()
    Dim r As Range, cfind As Range, j As Integer, add As String
    Dim k As Integer, m As Integer, c As Range
    Application.ScreenUpdating = False
    Worksheets("sheet1").Cells.Clear
    Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")

    Worksheets("sheet1").Activate

    Set r = Range(Range("a1"), Range("a1").End(xlDown))
    r.EntireColumn.AutoFit
    For Each c In ActiveSheet.UsedRange
        If c = "" Then c = "X"
    Next c

    Set cfind = r.Cells.Find(what:="event", lookat:=xlPart)
    If Not cfind Is Nothing Then
        add = cfind.Address
        j = cfind.Row
        Set cfind = r.Cells.FindNext(cfind)
        k = cfind.Row
        For m = j + 1 To k - 1
            Range(Cells(m, "B"), Cells(m, "B").End(xlToRight)).Cut Cells(j, "A").End(xlToRight).Offset(0, 1)
        Next m
    End If
    Do
        j = k
        Set cfind = r.Cells.FindNext(cfind)
        If cfind Is Nothing Then Exit Do
        If cfind.Address = add Then Exit Do
        k = cfind.Row
        For m = j + 1 To k - 1
            Range(Cells(m, "B"), Cells(m, "B").End(xlToRight)).Cut Cells(j, "A").End(xlToRight).Offset(0, 1)
        Next m
    Loop
    j = Range("a1").End(xlDown).Row
    'MsgBox j

    For k = j To 2 Step -1
        'MsgBox Cells(k, "A").Address
        If InStr(Cells(k, "A"), "event") = 0 Then Cells(k, "A").EntireRow.Delete
    Next k
    MsgBox "macro over"
    Application.ScreenUpdating = True
End Sub


YOUR FEEDBACK PLEASE
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
1
This macro doesn't seem to work. I get an error message when I try to run it.

The message is run-time error '5': Invalid procedure or call argument.

The problem in the code seems to be in line 28.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
as the lines are not numbered I do not know what line 28 is.. posst the code statement

as the code was writeen in excel 2007 I do not know whether this is the problem (normally it should not be)
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
it worked in my computer excel 2007
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
1
The line that excel's debugger indicates is this one:

Set cfind = r.Cells.FindNext(cfind)
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
this means that there is no "event" string in column A.
did you change the configuration of your data. is the data originally In column A arre still l in column A or in some other column

if you have changed the data send small example of data if necessary with random daa for security reasons