Spreadsheet cleanup help

[Mike Miller]

Hi I have not done much excel programming but have a task I need some
help with. I have a worksheet with information that I need to copy
and maniplulate onto another spreadsheet. the data in worksheet one
looks as follows:

Row Col1 Col2 Col3
A B 1
1 2
3

Basically in the source worksheet you have 3 columns and in col3 you
have some text that has new line characters. i need to copy this row
to my new worksheet and split col3 at each new line charcter so that
it looks this in the new worksheet:

Row Col1 Col2 Col3
1 A B 1
2 2
3 3

Any quick way to do this with VBA would be very helpful

 

[Rick Rothstein]

Give this macro a try (changing the Source and Destination assignments as
needed)...

Sub SplitCellLines()
Dim Source As Range, Destination As Range, Lines As Variant

Set Source = Worksheets("Sheet4").Range("C1")
Set Destination = Worksheets("Sheet5").Range("C1")

Lines = Split(Source, vbLf)
Destination.Resize(UBound(Lines) + 1) = _
WorksheetFunction.Transpose(Split(Source, vbLf))
End Sub

 

[Mike Miller]

Give this macro a try (changing the Source and Destination assignments as
needed)...

Sub SplitCellLines()
  Dim Source As Range, Destination As Range, Lines As Variant

  Set Source = Worksheets("Sheet4").Range("C1")
  Set Destination = Worksheets("Sheet5").Range("C1")

  Lines = Split(Source, vbLf)
  Destination.Resize(UBound(Lines) + 1) = _
                     WorksheetFunction.Transpose(Split(Source, vbLf))
End Sub

Next question on this would be how to loop the above through a
spreadsheet with 5000 rows.

M

 

[Rick Rothstein]

For future questions that you may ask on these newsgroups, please do not
simplify your question for us... just ask the actual question you are
looking to get a solution for... that way, the volunteers here won't end up
wasting their time creating solutions for situations you don't actually care
about.

For the question you have now asked, give the following macro a try. To make
things easier for you to change in case the starting cell of C1 and the
destination cell of C1 were just simplifications of your actual layout, I
have included a set of Const (constant) statements where you can set the
relevant layout conditions.

Sub SplitCellLines()
Dim Cell As Range, Source As Range, Destination As Range
Dim X As Long, LastRow As Long, NextRow As Long, Lines As Variant

Const SourceStartRow As Long = 1
Const SourceColumn As String = "C"
Const SourceSheetName As String = "Sheet4"
Const DestinationStartRow As Long = 1
Const DestinationColumn As String = "C"
Const DestinationSheetName As String = "Sheet5"

Set Destination = Worksheets(DestinationSheetName). _
Cells(DestinationStartRow, DestinationColumn)
With Worksheets(SourceSheetName)
LastRow = .Cells(.Rows.Count, SourceColumn).End(xlUp).Row
For X = SourceStartRow To LastRow
Set Source = .Cells(X, SourceColumn)
Lines = Split(Source.Value, vbLf)
Destination.Offset(NextRow).Resize(UBound(Lines) + 1) = _
WorksheetFunction.Transpose(Split(Source.Value, vbLf))
NextRow = NextRow + UBound(Lines) + 1
Next
End With
End Sub

--
Rick (MVP - Excel)

Give this macro a try (changing the Source and Destination assignments as
needed)...

Sub SplitCellLines()
Dim Source As Range, Destination As Range, Lines As Variant

Set Source = Worksheets("Sheet4").Range("C1")
Set Destination = Worksheets("Sheet5").Range("C1")

Lines = Split(Source, vbLf)
Destination.Resize(UBound(Lines) + 1) = _
WorksheetFunction.Transpose(Split(Source, vbLf))
End Sub

Next question on this would be how to loop the above through a
spreadsheet with 5000 rows.

M