Hello,
Rick provided me with this VBA code which works great!
Basically - this code looks at three columns and splits columns two and three while simply copying down column one. Was I am trying to do is to keep the identical function of this code; but instead of splitting columns 2 and 3, I need it to split columns 3 and 4. And there will be a new column 2 where I need it to copy down just like column 1 of the original code.
So, just pretend that before I run the code above, I inserted a column between A [ID] and B [RiskLevel3] and it is called [Team]. I want [ID] and [Team] to be copied down while [RiskLevel3] and [LegalObl] are split.
Rick provided me with this VBA code which works great!
Code:
Sub ID_RiskLevel3_LegalObl()
Dim R As Long, X As Long, Z As Long, LastRow As Long, MaxNewRows As Long
Dim MaxCol2 As Long, MaxCol3 As Long, ID As String
Dim Data As Variant, Result As Variant, RL3 As Variant, LO As Variant
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Data = Range("A2:C" & LastRow).Value
For R = 1 To UBound(Data)
MaxCol2 = Len(Data(R, 2)) - Len(Replace(Data(R, 2), vbLf, ""))
MaxCol3 = Len(Data(R, 3)) - Len(Replace(Data(R, 3), vbLf, ""))
MaxNewRows = MaxNewRows + Application.Max(MaxCol2, MaxCol3) + 1
Next
ReDim Result(1 To MaxNewRows, 1 To 3)
For R = 1 To UBound(Data)
If Len(Data(R, 1)) > 0 And Data(R, 1) <> ID Then ID = Data(R, 1)
RL3 = Split(Data(R, 2), vbLf)
LO = Split(Data(R, 3), vbLf)
If UBound(RL3) > UBound(LO) Then
ReDim Preserve LO(0 To UBound(RL3))
ElseIf UBound(RL3) < UBound(LO) Then
ReDim Preserve RL3(0 To UBound(LO))
End If
For Z = 0 To UBound(RL3)
X = X + 1
Result(X, 1) = ID
Result(X, 2) = RL3(Z)
Result(X, 3) = LO(Z)
Next
Next
Range("A2").Resize(UBound(Result), 3) = Result
End SubBasically - this code looks at three columns and splits columns two and three while simply copying down column one. Was I am trying to do is to keep the identical function of this code; but instead of splitting columns 2 and 3, I need it to split columns 3 and 4. And there will be a new column 2 where I need it to copy down just like column 1 of the original code.
So, just pretend that before I run the code above, I inserted a column between A [ID] and B [RiskLevel3] and it is called [Team]. I want [ID] and [Team] to be copied down while [RiskLevel3] and [LegalObl] are split.
