Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Hello,
OS:WINXP/Office2000
I have an excel sheet with 5419 rows and the column E has numbers that increases after certain rows. Eg: All rows from row 1 to row 9, is "1" then all rows from row 10 to row 34 has "2" and so on..
I need to write a macro to insert a line break after the row that ends with 1 and similarily for after the row that ends with 2 and so on.. till the nth numbered rowCan anyone please help? Thanks
Verner
Yes but, before running the macro you must Cut column E and Paste it into column A for it to work as it only looks at column A.
You can cut&paste it back into column E when done.
You may have to indent the rows because it will not format correctly on this forum board.
Sub InsAftDupes()
Dim i As Long
i = 2
While Not IsEmpty(Cells(i, 1))
If Cells(i, 1) <> Cells(i - 1, 1) Then
Rows(i).Insert (xlDown)
i = i + 2
Else
i = i + 1
End If
WendEnd Sub
Or, you can download a copy of the workbook here that contains the macro by right clicking on the link and selecting Save Target As.
Note: I did not write this macro. I got it from another helpful person somewhere else.
Regards,
Bryan
0 
Sorry, I forgot to mention that this macro was written in Excel 97 but I suspect it will work in 2K.
Please do let me know once you have a copy of it so I can remove it from my web page.
Bryan
0
Hi Brian,
Thanks for the Macro, I've made a copy.
I'll tell you once I've tried it, thanks a lot.Cheers
Verner
0
Good day Bryan.
I tried the macro, but it makes a line break after every line:
The result was this:
164879.000000 5273733.000000 1 1.500000164826.000000 5273945.000000 1 1.500000
163541.000000 5273019.000000 2 1.500000
163720.000000 5274804.000000 2 1.500000
163987.000000 5276409.000000 3 1.500000
163987.000000 5276409.000000 3 1.500000
The Requirement is this:
164879.000000 5273733.000000 1 1.500000
164826.000000 5273945.000000 1 1.500000163541.000000 5273019.000000 2 1.500000
163720.000000 5274804.000000 2 1.500000163987.000000 5276409.000000 3 1.500000
163987.000000 5276409.000000 3 1.500000I would need to have a line break after all the 1's, 2's,... nth
Please help.Cheers
Verner
0
Verner,
Before running the macro you must move the column with the numbers in question to column A as the macro only looks in column A.
When done you can move it back to it's desired column.
HTH
Bryan
0
Bryan,
Many thanks, but I cannot move it cause the data has been entered after merging the cells horizontally and entering data with spaces between them:
172546.000000 5274269.000000 1 1.500000
170673.000000 5272841.000000 1 1.500000
169248.000000 5272217.000000 1 1.500000
172546.000000 5274269.000000 2 1.500000
170673.000000 5272841.000000 2 1.500000
169248.000000 5272217.000000 2 1.500000So I cannot select the row of the singular numbers alone, if you know what I mean.
??
Thanks
Verner
0
Well, that makes it a lot harder but still can be done if interested.
Let's assume that all of the data is now in column A and starting in A1 as shown for this exercise.
Insert a new column A. Your data will now be in column B even though they are several merged cells.
In A1 use the formula:
=mid(b1,30,1)
That says to go to the 30th character in B1 and return one character to column A being the number one.
Drag that down until the old column E (values in question) gets to the value of 9.Then change the formula to return two characters being 11-99. Drag that down until it gets to triple digits and then change the formula to get three characters and so forth. It shouldn't take too much effort to accomplish this task.
When complete Copy column A and Paste Special, Values back to column A.
You should now have the values in question in column A adjacent to the original merged cells with their values in them in column B.
Run the marco now.
When done, delete column A.
HTH
Bryan
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
