Macro to move rows in excel automatically

Closed
excelhelp33
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
- Nov 19, 2012 at 06:46 PM
excelhelp33
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
- Nov 25, 2012 at 08:00 PM
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

excelhelp33
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
1
Nov 25, 2012 at 08:00 PM
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
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Nov 19, 2012 at 10:32 PM
is there any logic for these transfers. give one example
0
excelhelp33
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
1
Nov 20, 2012 at 05:57 AM
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.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Nov 20, 2012 at 09:46 PM
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
0

Didn't find the answer you are looking for?

Ask a question
excelhelp33
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
1
Nov 24, 2012 at 05:11 PM
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.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Nov 24, 2012 at 08:22 PM
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)
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Nov 24, 2012 at 08:22 PM
it worked in my computer excel 2007
0
excelhelp33
Posts
5
Registration date
Monday November 19, 2012
Status
Member
Last seen
November 25, 2012
1
Nov 25, 2012 at 12:45 PM
The line that excel's debugger indicates is this one:

Set cfind = r.Cells.FindNext(cfind)
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Nov 25, 2012 at 07:28 PM
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
-1