Asked by XGIS
at 2024-12-01 16:33:25
Point:500 Replies:9 POST_ID:828860USER_ID:11770
Topic:
Microsoft Excel Spreadsheet Software;Miscellaneous Programming;Spreadsheet Software
From This
To This. Please note rows must format with no row spaces
Author: XGIS replied at 2024-12-02 00:13:56
Hello Andrew, Nothing short of spectacular...Thankyou for the extra input.
Expert: andrew_man replied at 2024-12-01 20:53:41
Sub SplitAll()
Dim src As Range
Dim result As Variant
For Each src In Range("M:M").SpecialCells(xlCellTypeConstants)
result = Split(Application.WorksheetFunction.Substitute(src, "<", "[<"), "[")
'last cell in column M
For i = 1 To UBound(result)
result(i - 1) = result(i)
Next i
ReDim Preserve result(UBound(result) - 1)
Sheets("Sheet2").Select
With Cells(Rows.Count, 1).End(xlUp).Offset(0, 0)
Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
End With
Sheets("Sheet1").Select
Next src
End Sub
Dim src As Range
Dim result As Variant
For Each src In Range("M:M").SpecialCells(xlCellTypeConstants)
result = Split(Application.WorksheetFunction.Substitute(src, "<", "[<"), "[")
'last cell in column M
For i = 1 To UBound(result)
result(i - 1) = result(i)
Next i
ReDim Preserve result(UBound(result) - 1)
Sheets("Sheet2").Select
With Cells(Rows.Count, 1).End(xlUp).Offset(0, 0)
Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
End With
Sheets("Sheet1").Select
Next src
End Sub
Author: XGIS replied at 2024-12-01 20:19:46
Thankyou all for your input.. Special thanks to Andrew_Man for solving this issue.
I ended up just using F5 - Special - Blanks to remove lines and Trim and Clean to tidy the output.... Cheers Aaron
I ended up just using F5 - Special - Blanks to remove lines and Trim and Clean to tidy the output.... Cheers Aaron
Author: XGIS replied at 2024-12-01 19:56:45
Sub SplitAll()
Dim src As Range
Dim result As Variant
For Each src In Range("M:M").SpecialCells(xlCellTypeConstants)
result = Split(Application.WorksheetFunction.Substitute(src, "<", "[<"), "[")
'last cell in column M
For i = 1 To UBound(result)
result(i - 1) = result(i)
Next i
ReDim Preserve result(UBound(result) - 1)
Sheets("Sheet2").Select
With Cells(Rows.Count, 1).End(xlUp).Offset(0, 0)
Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
End With
Sheets("Sheet1").Select
Next src
End Sub
Dim src As Range
Dim result As Variant
For Each src In Range("M:M").SpecialCells(xlCellTypeConstants)
result = Split(Application.WorksheetFunction.Substitute(src, "<", "[<"), "[")
'last cell in column M
For i = 1 To UBound(result)
result(i - 1) = result(i)
Next i
ReDim Preserve result(UBound(result) - 1)
Sheets("Sheet2").Select
With Cells(Rows.Count, 1).End(xlUp).Offset(0, 0)
Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
End With
Sheets("Sheet1").Select
Next src
End Sub
Accepted Solution
Expert: andrew_man replied at 2024-12-01 18:40:35
500 points EXCELLENT
The result moves to sheet2 now.....
Expert: andrew_man replied at 2024-12-01 18:29:51
Hi all,
Okay!
amended!
Sub SplitAll()
Dim src As Range
Dim result As Variant
For Each src In Range("M:M").SpecialCells(xlCellTypeConstants)
result = Split(Application.WorksheetFunction.Substitute(src, "<", "[<"), "[")
'last cell in column M
With Cells(Rows.Count, 3).End(xlUp)
Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
End With
Next src
End Sub
Andrew Man from Hong Kong
Okay!
amended!
Sub SplitAll()
Dim src As Range
Dim result As Variant
For Each src In Range("M:M").SpecialCells(xlCellTypeConstants)
result = Split(Application.WorksheetFunction.Substitute(src, "<", "[<"), "[")
'last cell in column M
With Cells(Rows.Count, 3).End(xlUp)
Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
End With
Next src
End Sub
Andrew Man from Hong Kong
Expert: andrew_man replied at 2024-12-01 18:12:18
HI
okay
Andrew
okay
Andrew
Author: XGIS replied at 2024-12-01 18:00:10
Pls see the attached spreadsheet. The pasted BOLD values are static. This is the required output. Every time it sees a "<" the XML field starts a new row. It would be beneficial if it did it on the next sheet as it will obviously be quite long depending on how many rows I generate.
This sample is designed to generate testing data for a C# program.
I found this sample of code that may help but am not sure how to implement it in Excel 2012.
I need it to read all rows on the first sheet. and migrate them to the second sheet.
This sample is designed to generate testing data for a C# program.
I found this sample of code that may help but am not sure how to implement it in Excel 2012.
I need it to read all rows on the first sheet. and migrate them to the second sheet.
Expert: duncanb7 replied at 2024-12-01 17:40:19
Why your code is no closing tag in XML format ?
And what do you want for transpose ?
You want the tranpose output on Excel or Website ?
If display xml doc on website, just save the following as doc.xml and run it
on website ? and it can be displayed on Excel too but not at such your format
Hope you can clarify your question more or clearly
Duncan
And what do you want for transpose ?
You want the tranpose output on Excel or Website ?
If display xml doc on website, just save the following as doc.xml and run it
on website ? and it can be displayed on Excel too but not at such your format
Hope you can clarify your question more or clearly
Duncan
