FryGirl
Well-known Member
- Joined
- Nov 11, 2008
- Messages
- 1,364
- Office Version
- 365
- 2016
- Platform
- Windows
In column A, I have a list of tasks. Some rows have multiple tasks which need to be broken out into their own rows. In the example below, there are 3 rows. Row 2 has 6 tasks in the one line. It should actually be 8 rows.
If there was only two tasks in one row, I was able to do that with the following macro, just not sure about with 3 or more.
Before
A4.25.1.7. MAINTAINS SPILL RESPONSE KIT.
A4.2.138. REPAIRS REGENERATIVE AIR SWEEPER ELECTRICAL COMPONENT. A4.2.139. ADJUSTS REGENERATIVE AIR SWEEPER HYDRAULIC COMPONENT. A4.2.140. ISOLATES REGENERATIVE AIR SWEEPER HYDRAULIC MALFUNCTION. A4.2.141. REPAIRS REGENERATIVE AIR SWEEPER HYDRAULIC COMPONENT. A4.2.142. ADJUSTS REGENERATIVE AIR SWEEPER MECHANICAL COMPONENT. A4.2.143. ISOLATES REGENERATIVE AIR SWEEPER MECHANICAL SYSTEM MALFUNCTION.
A4.25.1.4. ATTENDS VARIOUS ENVIRONMENTAL PROTECTION COMMITTEE MEETING AND DISSEMINATES INFORMATION.
After
A4.25.1.7. MAINTAINS SPILL RESPONSE KIT.
A4.2.138. REPAIRS REGENERATIVE AIR SWEEPER ELECTRICAL COMPONENT.
A4.2.139. ADJUSTS REGENERATIVE AIR SWEEPER HYDRAULIC COMPONENT.
A4.2.140. ISOLATES REGENERATIVE AIR SWEEPER HYDRAULIC MALFUNCTION.
A4.2.141. REPAIRS REGENERATIVE AIR SWEEPER HYDRAULIC COMPONENT.
A4.2.142. ADJUSTS REGENERATIVE AIR SWEEPER MECHANICAL COMPONENT.
A4.2.143. ISOLATES REGENERATIVE AIR SWEEPER MECHANICAL SYSTEM MALFUNCTION.
A4.25.1.4. ATTENDS VARIOUS ENVIRONMENTAL PROTECTION COMMITTEE MEETING AND DISSEMINATES INFORMATION.
If there was only two tasks in one row, I was able to do that with the following macro, just not sure about with 3 or more.
VBA Code:
Sub SplitRow()
Dim i As Long
Dim LastRow As Long: LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 2 Step -1
Dim pos1 As Long: pos1 = InStr(1, Range("A" & i), " A4")
If pos1 > 0 Then
Rows(i + 1).Insert
Cells(i + 1, 1).Value = Right(Cells(i, 1).Value, Len(Cells(i, 1)) - InStr(pos1, Cells(i, 1).Value, ""))
Cells(i, 1).Value = Left(Cells(i, 1).Value, pos1)
End If
Next i
End Sub
Before
A4.25.1.7. MAINTAINS SPILL RESPONSE KIT.
A4.2.138. REPAIRS REGENERATIVE AIR SWEEPER ELECTRICAL COMPONENT. A4.2.139. ADJUSTS REGENERATIVE AIR SWEEPER HYDRAULIC COMPONENT. A4.2.140. ISOLATES REGENERATIVE AIR SWEEPER HYDRAULIC MALFUNCTION. A4.2.141. REPAIRS REGENERATIVE AIR SWEEPER HYDRAULIC COMPONENT. A4.2.142. ADJUSTS REGENERATIVE AIR SWEEPER MECHANICAL COMPONENT. A4.2.143. ISOLATES REGENERATIVE AIR SWEEPER MECHANICAL SYSTEM MALFUNCTION.
A4.25.1.4. ATTENDS VARIOUS ENVIRONMENTAL PROTECTION COMMITTEE MEETING AND DISSEMINATES INFORMATION.
After
A4.25.1.7. MAINTAINS SPILL RESPONSE KIT.
A4.2.138. REPAIRS REGENERATIVE AIR SWEEPER ELECTRICAL COMPONENT.
A4.2.139. ADJUSTS REGENERATIVE AIR SWEEPER HYDRAULIC COMPONENT.
A4.2.140. ISOLATES REGENERATIVE AIR SWEEPER HYDRAULIC MALFUNCTION.
A4.2.141. REPAIRS REGENERATIVE AIR SWEEPER HYDRAULIC COMPONENT.
A4.2.142. ADJUSTS REGENERATIVE AIR SWEEPER MECHANICAL COMPONENT.
A4.2.143. ISOLATES REGENERATIVE AIR SWEEPER MECHANICAL SYSTEM MALFUNCTION.
A4.25.1.4. ATTENDS VARIOUS ENVIRONMENTAL PROTECTION COMMITTEE MEETING AND DISSEMINATES INFORMATION.