decadence
Well-known Member
- Joined
- Oct 9, 2015
- Messages
- 525
- Office Version
- 365
- 2016
- 2013
- 2010
- 2007
- Platform
- Windows
Hi, I am trying to separate comma delimited cells into rows while keeping the data adjacent to it the same, however I have a non static range where the columns might be in a different place so I can't use the standard range up on specific columns.
Basically I am using a header and setting that column as the range, then I want to step through the cells and split out the cells into rows, and change the quantity to match the split out cells, see below for Example. Can someone help with this please
From This
To This
What I have so far is this......
Basically I am using a header and setting that column as the range, then I want to step through the cells and split out the cells into rows, and change the quantity to match the split out cells, see below for Example. Can someone help with this please
From This
MtlSeq | Revision | MfgComment | PurComment | QtyPer |
10 | D | R151,R69_1,R177 | Generic | 3 |
20 | D | R188 | Electrical | 1 |
30 | D | R1, R3 | Generic | 2 |
To This
MtlSeq | Revision | MfgComment | PurComment | QtyPer |
10 | D | R151 | Generic | 1 |
10 | D | R69_1 | Generic | 1 |
10 | D | R177 | Generic | 1 |
20 | D | R188 | Electrical | 1 |
30 | D | R1 | Generic | 1 |
30 | D | R3 | Generic | 1 |
What I have so far is this......
VBA Code:
Option Explicit
Dim Rng As Range, Fnd As Range, xVal As Range, vSplit
Sub SplitRef()
'Split References
Set Rng = RngMfg
For Each xVal in Rng
vSplit = Split(xVal, ",")
''''''''''''''''''''''''''''''''''''''''''''''''Split into Rows
Next xVal
End Sub
Function RngMfg() As Range
Set Fnd = ActiveSheet.Columns.Find(what:="MfgComment", LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not Fnd Is Nothing Then
Set RngMfg = Range(Fnd.Offset(1), Cells(Rows.Count, Fnd.Column).End(xlUp))
End If
End Function