I've struggled with online examples for several days now. The closest example by its description is the "Insert and Fill series" available here, but it does not activate in Excel 07 (written originally for 97)
Task at hand. Any advice?
I need to insert rows where a series of numbers, as part of a row of data, are not consecutive. I prefer to run the macro on activecells, since I have some fractional numbers in the overall series (such as 368.1, 368.2, 369).
Currently: (X=other associated data)
1 X 45 X...X
2 X 48 X...X
3 X 49 X...X
4 X 51 X...X
Desired:
1 X 45 X...X
2 46
3 47
4 X 48 X...X
5 X 49 X...X
6 50
7 X 51 X...X
Try this maco:
Code:Sub test() Dim WorkRows As Long, _ Ndx As Long, _ Diff As Long, _ InsertCounter As Integer, _ WorkColumn As String WorkColumn = "G" ' <<<<<<< CHANGE TO YOUR COLUMN WorkRows = Cells(Rows.Count, WorkColumn).End(xlUp).Row 'Start at the bottom of the list and work up to the top 'that way ndx will always poin to the row just above the ones 'that were inserted For Ndx = WorkRows To 2 Step -1 Cells(Ndx, WorkColumn).Activate Diff = Cells(Ndx, WorkColumn).Value - Cells(Ndx - 1, WorkColumn).Value If Diff > 1 Then For InsertCounter = 1 To Diff - 1 Range(WorkColumn & Ndx).EntireRow.Insert ActiveCell.Value = ActiveCell.Offset(1, 0).Value - 1 Next InsertCounter End If Next Ndx End Sub
---
Ben Van Johnson
It took a few tests and verifying my series, but it did work, thanks!
I had to take large chunks of the data into a new worksheet to run the macro, since I found several areas where the number series contained a non-sequential number (either a needed repeat or a decimal). Cut'n'paste put everything back to rights.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks