excel - Copy data from a column into a new column with loop -

rows  | ref1    |   ref2  | ref3|   final ref     3     |   aa    |    bb   | cc  |   aa     3     |   aa    |    bb   | cc  |   bb     3     |   aa    |    bb   | cc  |   cc     2     |      |    al   |     |       2     |      |    al   |     |   al 

the vba below automates creation of 'final ref' column. value taken other columns. e.g. there 3 duplicate rows same references; need ref1 first row, ref2 second row , ref3 third row. next 2 rows, duplicates, need ref1 first row , ref2 second row etc.

this have far. i'd range dynamic, based on longest row. have type in value @ moment. have vba separately select used range don't know how combine two. in advance.

sub copyrowpasteref() dim rngtoconvert range dim rngrow range dim rngcell range  'incremental step keep track of rows dim writerow integer writerow = 1  'the entire range converting set rngtoconvert = sheets("sheet11").range("a1:e6")  'loop through each row each rngrow in rngtoconvert.rows      'loop through each cell (field)     each rngcell in rngrow.cells          'ignore first row         if rngcell.column > 1 , rngcell.value <> ""              'write row header             sheets("sheet12").cells(writerow, 1).value = rngrow.cells(1, 1)              'write non-null value             sheets("sheet12").cells(writerow, 2).value = rngcell.value              'increment counter             writerow = writerow + 1         end if     next rngcell next rngrow end sub 

how can add vba below vba above incorporate dynamic range based on used cells?

range("a1").resize(cells.find(what:="*", searchorder:=xlrows, _   searchdirection:=xlprevious, lookin:=xlvalues).row, _   cells.find(what:="*", searchorder:=xlbycolumns, _   searchdirection:=xlprevious, lookin:=xlvalues).column).select 

put in e2 , copy down:

=index(b2:d2,countifs($b$1:b2,"=" &b2,$c$1:c2,"=" & c2,$d$1:d2,"=" &d2))