Macro to move rows in excel automatically
Closed
excelhelp33
excelhelp33
- Posts
- 5
- Registration date
- Monday November 19, 2012
- Status
- Member
- Last seen
- November 25, 2012
excelhelp33
- Posts
- 5
- Registration date
- Monday November 19, 2012
- Status
- Member
- Last seen
- November 25, 2012
Related:
- How to move rows automatically in excel based on cell value
- Excel move row to another sheet based on cell value automatically - Best answers
- Automatically move rows to another sheet excel - Best answers
- Code to move rows into another sheet based on certain values ✓ - Forum - Excel
- Copy data from one excel sheet to another: automatically - Guide
- How do i automatically add rows in excel based on cell value - Forum - Excel
- Excel move row to another sheet based on cell value automatically ✓ - Forum - Office Software
- Copy rows to other sheets based on value in column ✓ - Forum - Excel
9 replies
excelhelp33
Nov 25, 2012 at 08:00 PM
- Posts
- 5
- Registration date
- Monday November 19, 2012
- Status
- Member
- Last seen
- November 25, 2012
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?
What else could be causing the problem?
venkat1926
Nov 19, 2012 at 10:32 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Nov 19, 2012 at 10:32 PM
is there any logic for these transfers. give one example
excelhelp33
Nov 20, 2012 at 05:57 AM
- Posts
- 5
- Registration date
- Monday November 19, 2012
- Status
- Member
- Last seen
- November 25, 2012
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.
venkat1926
Nov 20, 2012 at 09:46 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
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
YOUR FEEDBACK PLEASE
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
Didn't find the answer you are looking for?
Ask a question
excelhelp33
Nov 24, 2012 at 05:11 PM
- Posts
- 5
- Registration date
- Monday November 19, 2012
- Status
- Member
- Last seen
- November 25, 2012
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.
The message is run-time error '5': Invalid procedure or call argument.
The problem in the code seems to be in line 28.
venkat1926
Nov 24, 2012 at 08:22 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
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)
as the code was writeen in excel 2007 I do not know whether this is the problem (normally it should not be)
venkat1926
Nov 24, 2012 at 08:22 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Nov 24, 2012 at 08:22 PM
it worked in my computer excel 2007
excelhelp33
Nov 25, 2012 at 12:45 PM
- Posts
- 5
- Registration date
- Monday November 19, 2012
- Status
- Member
- Last seen
- November 25, 2012
Nov 25, 2012 at 12:45 PM
The line that excel's debugger indicates is this one:
Set cfind = r.Cells.FindNext(cfind)
Set cfind = r.Cells.FindNext(cfind)
venkat1926
Nov 25, 2012 at 07:28 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
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
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