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.
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!
Related:
How to automatically move cells in excel
Automatically move to next row in excel - Best answers
How to move rows automatically in excel based on cell value - Best answers
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.
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
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
