vba - Paste one array (single dimension) with >100K item into a excel range -

i posted same question on stackoverflow thread think here correct place ask (if not right, admin please remove it).every day need format date imported as400 (data, time,..). usualy (for thousands of record) use code.

public sub cfn_formatdate(control iribboncontrol) application.screenupdating = false     dim ur long, x long     dim mycol integer     mycol = activecell.column     ur = cells(rows.count, mycol).end(xlup).row     x = 2 ur         if not isdate(cells(x, mycol))             select case len(cells(x, mycol))             case 8                 cells(x, mycol) = dateserial(left(cells(x, mycol), 4), mid(cells(x, mycol), 5, 2), right(cells(x, mycol), 2))             case 6                 cells(x, mycol) = dateserial(left(cells(x, mycol), 2), mid(cells(x, mycol), 3, 2), right(cells(x, mycol), 2))             end select         end if     next x columns(mycol).numberformat = "dd/mm/yyyy;@" columns(mycol).entirecolumn.autofit application.screenupdating = true end sub 

but if records many more, code posted code not performing. (ex 70k records formatted / pasted in 18 seconds) thought using variables in array , wrote code:

sub convdate(c integer) application.screenupdating = false dim lrw long, long dim arrval variant lrw = activesheet().range(cells(1, c)).end(xldown).row redim arrval(2 lrw) = 2 lrw     if isdate(cells(i, c))         arrval(i) = cells(i, c)     else         select case len(cells(i, c)) ' check yyyymmdd or yymmdd             case 8                 arrval(i) = dateserial(left(cells(i, c), 4), mid(cells(i, c), 5, 2), right(cells(i, c), 2))             case 6                 arrval(i) = dateserial(left(cells(i, c), 2), mid(cells(i, c), 3, 2), right(cells(i, c), 2))         end select     end if nextx:     next range(cells(2, c), cells(lrw, c)) = arrval columns(c).numberformat = "dd/mm/yyyy;@" columns(c).entirecolumn.autofit application.screenupdating = true end sub 

it not work, cells (in range) have same result (cells(2, c)). guy suggested me change code like:

activesheet.range(cells(2, c), cells(lrw, c)).value = worksheetfunction.transpose(arrval) 

this change limiting , on 65536 records error (runtime 13, type mismatch)

ok, summarise answers , comments:

1. as have indicated in question , user85489 alludes, reading values array, manipulating same array, , writing sheet vastly quicker lopping cell cell.
2. if have array 'row' dimension not going change. might fair you're better off declaring 2 dimensional array of size (1 rows, 1 columns). way can avoid having transpose 1 dimensional array @ all.
3. because gareth points out, transpose() limited 65536 elements in dimension.

putting together, then, skeleton code post this:

sub convertdates(colindex long)     dim v variant     dim firstcell range     dim lastcell range     dim fullrange range     dim long     dim dd integer     dim mm integer     dim yy integer     dim dat date      'define range     thisworkbook.worksheets("sheet1")         set firstcell = .cells(2, colindex)         set lastcell = .cells(.rows.count, colindex).end(xlup)         set fullrange = .range(firstcell, lastcell)     end      'read values array     v = fullrange.value      'convert text values dates     = 1 ubound(v, 1)         if not isdate(v(i, 1))             if len(v(i, 1)) = 6 v(i, 1) = "20" & v(i, 1)             yy = cint(left(v(i, 1), 4))             mm = cint(mid(v(i, 1), 5, 2))             dd = cint(right(v(i, 1), 2))             dat = dateserial(yy, mm, dd)             v(i, 1) = dat         end if     next      'write revised array , format range     fullrange         .numberformat = "dd/mm/yyyy;@"         .value = v         .entirecolumn.autofit     end  end sub