excel - Taking rows of data and converting into columns with consecutive rows -

i've seen similar posts not quite need or understand solve simple problem.

i have hundreds of rows of data i'd transform columns. original data 2 empty rows between , sets of related data can vary in length:

9 8 7 6 5 4 3 2 1   j h g f     e d c b 

i'd able reverse order of each set , transpose them in columns going down row each data set so:

1   2   3   4   5   6   7   8   9       b   c   d   e   f   g   h     j 

i had success first part using simple formula =offset($a$2,counta(a:a)-row(),0) because wasn't sure how in vba.

the code i'm using grab data , transpose, i'm having trouble getting go down row each unique data set. here's code i'm trying use, doesn't seem work , start running down worksheet until macro craps out.

sub transposerange()  dim inrange range  dim outrange range  dim long   set inrange = sheets("output").range("a3:a10002")  set outrange = sheets("output").range("h2:ntr2")   = 1 10000 step 1   outrange.cells(1, i) = inrange.cells(i, 1)         activecell.offset(1, 0).select  next  end sub 

i'm sure there's obvious , simple i'm missing alas i'm still noob in training. suggestions appreciated.

this code assumes data constants, , uses vba's wonderful specialcells property break out each chunk in column 1. uses array, faster looping through cells:

sub transposecolumnsections() dim ws excel.worksheet dim lastrow long dim columnconstants excel.range dim long dim columnarea excel.range dim arearowscount long dim reversedconstants() variant dim j long  set ws = activesheet ws     lastrow = .range("a" & .rows.count).end(xlup).row     set columnconstants = .columns(1).specialcells(xlcelltypeconstants)     = 1 columnconstants.areas.count         set columnarea = columnconstants.areas(i)         arearowscount = columnarea.rows.count         redim reversedconstants(1 arearowscount)         j = arearowscount 1 step -1             reversedconstants(arearowscount - (j - 1)) = columnarea(j).value         next j         .cells(i, 2).resize(1, arearowscount) = reversedconstants     next     .columns(1).delete end end sub