I have a sheet with columns from A:H. I need to split column H every 60 characters into a new row and copy A:G into these new rows.
Here's how the data is and how I want it:
In a perfect world there would be a column at the start that would count each row for each message. So column A1 would be 1, A2 would be 2, A3 would be 1, A4 would be 2, etc.
There are 29,453 rows that I need to do this to, so manually doing this is not really an option.
Any help, please?
It's not really that difficult.
First, store all the log note in an array
Second, loop through every log note in the array and for every loop, do a do while loop to insert new row; copy values to new row; splitting the string every 60 characters.
Continue to loop as long as the length of the substring is more than 60.
Third, for the spliting of the cell for column H; this can be done by using the Mid Function to display the values based on characer position.
For example, first row we will display from character position 1 to 60
eg. Mid(subString, 1, 60)
Whereas for second row we will display from character position 61 onwards
eg. Mid(subString, 61, Len(subString))
Then for subsequent do while loops, the second row will display from character position 1 to 60 etc.
Info on Mid Function:
Mid(string, start, length)
string = full original text
start = start position of character
length = length of character
Info on Cell Offset:
.Cells(rowNo, colNo).Offset(RowOffset, ColumnOffset)
RowOffset = eg. [1 = 1 row below cell] [-1 = 1 row above cell]
ColumnOffset = eg. [1 = 1 col to the right of cell] [-1 = 1 col to the left of cell]
Full Solution
Option Explicit
'split every 60 characters new row
Sub SplitEverySixtyChar()
Dim ws As Worksheet
Dim rowNo As Long
Dim lastRowNo As Long
Dim arrayLogNote As Variant
Dim logNote As Variant
Dim subString As String
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
'Find last row no
lastRowNo = (.Cells(.Rows.Count, 1).End(xlUp).Row)
'Store all log note in array
arrayLogNote = .Range("H2:H" & lastRowNo).Value
'Starting row no is 2
rowNo = 2
For Each logNote In arrayLogNote
subString = CStr(logNote)
Do While Len(subString) > 60
'Insert new row
.Cells(rowNo, 1).Offset(1, 0).EntireRow.Insert
'Copy the cell from A:G col into new row
.Cells(rowNo, 1).Offset(1, 0).Value = .Cells(rowNo, 1).Value
.Cells(rowNo, 2).Offset(1, 0).Value = .Cells(rowNo, 2).Value
.Cells(rowNo, 3).Offset(1, 0).Value = .Cells(rowNo, 3).Value
.Cells(rowNo, 4).Offset(1, 0).Value = .Cells(rowNo, 4).Value
.Cells(rowNo, 5).Offset(1, 0).Value = .Cells(rowNo, 5).Value
.Cells(rowNo, 6).Offset(1, 0).Value = .Cells(rowNo, 6).Value
.Cells(rowNo, 7).Offset(1, 0).Value = .Cells(rowNo, 7).Value
'Display text for new row from character position 60 onwards
.Cells(rowNo, 8).Offset(1, 0).Value = Mid(subString, 61, Len(subString))
'Display text from character position 1 to 60
.Cells(rowNo, 8).Value = Mid(subString, 1, 60)
subString = .Cells(rowNo, 8).Offset(1, 0).Value
'Increment Row No
rowNo = rowNo + 1
Loop
'Increment Row No
rowNo = rowNo + 1
Next logNote
End With
End Sub
Note: This is probably not the most efficient solution out there. There are definitely better solution than this.
