Excel VBA Split Column at Certain Length into Multiple Rows -

i have sheet columns a:h. need split column h every 60 characters new row , copy a:g these new rows.

here's how data , how want it:

in perfect world there column @ start count each row each message. column a1 1, a2 2, a3 1, a4 2, etc.

there 29,453 rows need to, manually doing not option.

any help, please?

it's not difficult.

first, store log note in array

second, loop through every log note in array , every loop, do while loop insert new row; copy values new row; splitting string every 60 characters.

continue loop long length of substring more 60.

third, spliting of cell column h; can done using mid function display values based on characer position.

for example, first row display character position 1 60

eg. mid(substring, 1, 60)

whereas second row display character position 61 onwards

eg. mid(substring, 61, len(substring))

then subsequent while loops, second row display character position 1 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 right of cell] [-1 = 1 col left of cell]

full solution

option explicit  'split every 60 characters new row sub spliteverysixtychar()      dim ws worksheet     dim rowno long     dim lastrowno long     dim arraylognote variant     dim lognote variant     dim substring string       set ws = thisworkbook.sheets("sheet1")      ws          'find last row no         lastrowno = (.cells(.rows.count, 1).end(xlup).row)          'store log note in array         arraylognote = .range("h2:h" & lastrowno).value          'starting row no 2         rowno = 2          each lognote in arraylognote              substring = cstr(lognote)              while len(substring) > 60                  'insert new row                 .cells(rowno, 1).offset(1, 0).entirerow.insert                  'copy cell a:g col 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 new row character position 60 onwards                 .cells(rowno, 8).offset(1, 0).value = mid(substring, 61, len(substring))                  'display text character position 1 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  end sub 

note: not efficient solution out there. there better solution this.