Hello all,
I have a excel worksheet as :

Name Address Cost center Class
A 123 345,234 Q
B 122 34 Q
C 1 3222,34,56 Q

But now i want to write a macro so that i can separate the comma values
under the cost center column into different rows,
So my output looks like


Name Address Cost center Class
A 123 345 Q
A 123 234 Q
B 122 34 Q
C 1 3222 Q
C 1 34 Q
C 1 56 Q

Can this be done with the help of macro and once i get the data in this
format , i need to run some other macros too, so i want it into the
same worksheet if possible

Thanks

This seemed to work okay. Where FirstRow is the first row of data (excludes
header), DataCol is the Column whose data is being split (in this example,
I'm using column C, which is 3). Make sure your data is backed up and be
aware that this newsgroup often wraps code in funny places, so look for any
lines in red after you paste into a module and put back together (I tried to
wrap the longer lines using the line continuation character " _", but may not
help much.


Sub Test()
Const FirstRow As Long = 2
Const DataCol As Long = 3
Dim LastRow As Long
Dim i As Long
Dim varTemp As Variant

With Sheets("Sheet1")
LastRow = .Cells(.Rows.Count, DataCol).End(xlUp).Row
For i = LastRow To FirstRow Step -1
If InStr(1, .Cells(i, DataCol).Value, _
",", vbTextCompare) 0 Then
varTemp = Split(.Cells(i, DataCol).Value, _
",", -1, vbTextCompare)
.Range(.Cells(i, DataCol), _
.Cells(i, DataCol)(UBound(varTemp), _
1)).Offset(1, 0).EntireRow.Insert
.Range(.Cells(i, DataCol), _
.Cells(i, DataCol).Offset(UBound(varTemp), _
0)).Value = Application.Transpose(varTemp)
.Range(.Cells(i, .Cells(i, DataCol).CurrentRegion.Columns(1).Column), _
.Cells(i + UBound(varTemp), DataCol - 1)).FillDown
.Range(.Cells(i, DataCol + 1), _
.Cells(i + UBound(varTemp), DataCol + 1)).FillDown
End If
Next i
End With

End Sub

" wrote:

Hello all,
I have a excel worksheet as :

Name Address Cost center Class
A 123 345,234 Q
B 122 34 Q
C 1 3222,34,56 Q

But now i want to write a macro so that i can separate the comma values
under the cost center column into different rows,
So my output looks like


Name Address Cost center Class
A 123 345 Q
A 123 234 Q
B 122 34 Q
C 1 3222 Q
C 1 34 Q
C 1 56 Q

Can this be done with the help of macro and once i get the data in this
format , i need to run some other macros too, so i want it into the
same worksheet if possible

Thanks

Amit,
Does DataText To columns achieve the result ?

NickHK

wrote in message
ups.com...
Hello all,
I have a excel worksheet as :

Name Address Cost center Class
A 123 345,234 Q
B 122 34 Q
C 1 3222,34,56 Q

But now i want to write a macro so that i can separate the comma values
under the cost center column into different rows,
So my output looks like


Name Address Cost center Class
A 123 345 Q
A 123 234 Q
B 122 34 Q
C 1 3222 Q
C 1 34 Q
C 1 56 Q

Can this be done with the help of macro and once i get the data in this
format , i need to run some other macros too, so i want it into the
same worksheet if possible

Thanks

Hi,
Thanks for your help,
I tried using the code but its only working for the last row which have
multiple values and not for the first or the second row.
And i also wanted to know how can i extend this to more than 3 columns
which i have shown you till now coz i have to work on a file which have
atleast till Z columns and h ave to split on only columns


Please help

Thanks

JMB wrote:
This seemed to work okay. Where FirstRow is the first row of data (excludes
header), DataCol is the Column whose data is being split (in this example,
I'm using column C, which is 3). Make sure your data is backed up and be
aware that this newsgroup often wraps code in funny places, so look for any
lines in red after you paste into a module and put back together (I tried to
wrap the longer lines using the line continuation character " _", but may not
help much.


Sub Test()
Const FirstRow As Long = 2
Const DataCol As Long = 3
Dim LastRow As Long
Dim i As Long
Dim varTemp As Variant

With Sheets("Sheet1")
LastRow = .Cells(.Rows.Count, DataCol).End(xlUp).Row
For i = LastRow To FirstRow Step -1
If InStr(1, .Cells(i, DataCol).Value, _
",", vbTextCompare) 0 Then
varTemp = Split(.Cells(i, DataCol).Value, _
",", -1, vbTextCompare)
.Range(.Cells(i, DataCol), _
.Cells(i, DataCol)(UBound(varTemp), _
1)).Offset(1, 0).EntireRow.Insert
.Range(.Cells(i, DataCol), _
.Cells(i, DataCol).Offset(UBound(varTemp), _
0)).Value = Application.Transpose(varTemp)
.Range(.Cells(i, .Cells(i, DataCol).CurrentRegion.Columns(1).Column), _
.Cells(i + UBound(varTemp), DataCol - 1)).FillDown
.Range(.Cells(i, DataCol + 1), _
.Cells(i + UBound(varTemp), DataCol + 1)).FillDown
End If
Next i
End With

End Sub

" wrote:

Hello all,
I have a excel worksheet as :

Name Address Cost center Class
A 123 345,234 Q
B 122 34 Q
C 1 3222,34,56 Q

But now i want to write a macro so that i can separate the comma values
under the cost center column into different rows,
So my output looks like


Name Address Cost center Class
A 123 345 Q
A 123 234 Q
B 122 34 Q
C 1 3222 Q
C 1 34 Q
C 1 56 Q

Can this be done with the help of macro and once i get the data in this
format , i need to run some other macros too, so i want it into the
same worksheet if possible

Thanks

And I'm assuming the worksheet name is "Sheet1".


"JMB" wrote:

This seemed to work okay. Where FirstRow is the first row of data (excludes
header), DataCol is the Column whose data is being split (in this example,
I'm using column C, which is 3). Make sure your data is backed up and be
aware that this newsgroup often wraps code in funny places, so look for any
lines in red after you paste into a module and put back together (I tried to
wrap the longer lines using the line continuation character " _", but may not
help much.


Sub Test()
Const FirstRow As Long = 2
Const DataCol As Long = 3
Dim LastRow As Long
Dim i As Long
Dim varTemp As Variant

With Sheets("Sheet1")
LastRow = .Cells(.Rows.Count, DataCol).End(xlUp).Row
For i = LastRow To FirstRow Step -1
If InStr(1, .Cells(i, DataCol).Value, _
",", vbTextCompare) 0 Then
varTemp = Split(.Cells(i, DataCol).Value, _
",", -1, vbTextCompare)
.Range(.Cells(i, DataCol), _
.Cells(i, DataCol)(UBound(varTemp), _
1)).Offset(1, 0).EntireRow.Insert
.Range(.Cells(i, DataCol), _
.Cells(i, DataCol).Offset(UBound(varTemp), _
0)).Value = Application.Transpose(varTemp)
.Range(.Cells(i, .Cells(i, DataCol).CurrentRegion.Columns(1).Column), _
.Cells(i + UBound(varTemp), DataCol - 1)).FillDown
.Range(.Cells(i, DataCol + 1), _
.Cells(i + UBound(varTemp), DataCol + 1)).FillDown
End If
Next i
End With

End Sub

" wrote:

Hello all,
I have a excel worksheet as :

Name Address Cost center Class
A 123 345,234 Q
B 122 34 Q
C 1 3222,34,56 Q

But now i want to write a macro so that i can separate the comma values
under the cost center column into different rows,
So my output looks like


Name Address Cost center Class
A 123 345 Q
A 123 234 Q
B 122 34 Q
C 1 3222 Q
C 1 34 Q
C 1 56 Q

Can this be done with the help of macro and once i get the data in this
format , i need to run some other macros too, so i want it into the
same worksheet if possible

Thanks

I'm glad I got into the habit of copying replies before trying to post -darn
server keeps giving me error messages and losing everything I type.

Anyway -
I am assuming the data is formatted as text. If you enter 345,234 in a
cell, excel will assume it is a number and although it will display 345,234
on the screen the actual value is 345234 (no commas) and cannot be split by
the macro. Excel will treat 3222,34,56 as text since it is not in a
recognizable number format (therefore the commas are part of the data and not
viewed as some number format).

And i also wanted to know how can i extend this to more than 3 columns
which i have shown you till now coz i have to work on a file which have
atleast till Z columns and h ave to split on only columns

I don't follow. Do more than 3 columns have to be split across multiple rows
or do more than 3 columns have to have their gaps filled in after the data is
split?


" wrote:

Hi,
Thanks for your help,
I tried using the code but its only working for the last row which have
multiple values and not for the first or the second row.
And i also wanted to know how can i extend this to more than 3 columns
which i have shown you till now coz i have to work on a file which have
atleast till Z columns and h ave to split on only columns


Please help

Thanks

JMB wrote:
This seemed to work okay. Where FirstRow is the first row of data (excludes
header), DataCol is the Column whose data is being split (in this example,
I'm using column C, which is 3). Make sure your data is backed up and be
aware that this newsgroup often wraps code in funny places, so look for any
lines in red after you paste into a module and put back together (I tried to
wrap the longer lines using the line continuation character " _", but may not
help much.


Sub Test()
Const FirstRow As Long = 2
Const DataCol As Long = 3
Dim LastRow As Long
Dim i As Long
Dim varTemp As Variant

With Sheets("Sheet1")
LastRow = .Cells(.Rows.Count, DataCol).End(xlUp).Row
For i = LastRow To FirstRow Step -1
If InStr(1, .Cells(i, DataCol).Value, _
",", vbTextCompare) 0 Then
varTemp = Split(.Cells(i, DataCol).Value, _
",", -1, vbTextCompare)
.Range(.Cells(i, DataCol), _
.Cells(i, DataCol)(UBound(varTemp), _
1)).Offset(1, 0).EntireRow.Insert
.Range(.Cells(i, DataCol), _
.Cells(i, DataCol).Offset(UBound(varTemp), _
0)).Value = Application.Transpose(varTemp)
.Range(.Cells(i, .Cells(i, DataCol).CurrentRegion.Columns(1).Column), _
.Cells(i + UBound(varTemp), DataCol - 1)).FillDown
.Range(.Cells(i, DataCol + 1), _
.Cells(i + UBound(varTemp), DataCol + 1)).FillDown
End If
Next i
End With

End Sub

" wrote:

Hello all,
I have a excel worksheet as :

Name Address Cost center Class
A 123 345,234 Q
B 122 34 Q
C 1 3222,34,56 Q

But now i want to write a macro so that i can separate the comma values
under the cost center column into different rows,
So my output looks like


Name Address Cost center Class
A 123 345 Q
A 123 234 Q
B 122 34 Q
C 1 3222 Q
C 1 34 Q
C 1 56 Q

Can this be done with the help of macro and once i get the data in this
format , i need to run some other macros too, so i want it into the
same worksheet if possible

Thanks