Arranging the row data into Columns using VBA in excel? -

Arranging the row data into Columns using VBA in excel? -

i adapting off of question: re-arranging row info in columns

i have excel info set follows;

collection latdd londd date location method specie1 specie2 specie3(+-110 species columns in total) abs1 11.35 -10.3 2003-02-01 bucket 0 1 3 abs2 11.36 -10.4 2003-02-02 b stick 2 0 6

i info appear so:

collection specie count latdd londd date location method abs1 specie1 11.35 -10.3 2003-02-01 bucket abs1 specie2 1 11.35 -10.3 2003-02-01 bucket abs1 specie3 3 11.35 -10.3 2003-02-01 bucket abs2 specie1 2 11.36 -10.4 2003-02-02 b stick abs2 specie2 -11.36 -10.4 2003-02-02 b stick abs2 specie3 6 -11.36 -10.4 2003-02-02 b stick

i attempted adapt ripsters original vba code reply unfortunately unable figure how need alter it. could please advise me on how adjust code produce desired output?

here orginal vba code:

sub example() dim resources() string dim rng range dim row long dim col long dim x long redim resources(1 (activesheet.usedrange.rows.count - 1) * (activesheet.usedrange.columns.count - 1), 1 3) 'change source sheet sheets("sheet1").select 'read info array row = 2 activesheet.usedrange.rows.count col = 2 activesheet.usedrange.columns.count x = x + 1 resources(x, 1) = cells(row, 1).value ' name resources(x, 2) = cells(1, col).value ' date resources(x, 3) = cells(row, col).value ' value next next 'change destination sheet sheets("sheet2").select 'write info sheet range(cells(1, 1), cells(ubound(resources), ubound(resources, 2))).value = resources 'insert column headers rows(1).insert range("a1:c1").value = array("resource", "date", "value") 'set strings values set rng = range(cells(1, 3), cells(activesheet.usedrange.rows.count, 3)) rng.value = rng.value end sub

try this:

sub example() dim row long dim col long dim x long h1 = "sheet1" h2 = "sheet2" sheets(h1).select x = 2 'headers sheet2 sheets(h2).cells(1, 1).value = sheets(h1).cells(1, 1) sheets(h2).cells(1, 2).value = "specie" sheets(h2).cells(1, 3).value = "count" sheets(h2).cells(1, 4).value = sheets(h1).cells(1, 2) sheets(h2).cells(1, 5).value = sheets(h1).cells(1, 3) sheets(h2).cells(1, 6).value = sheets(h1).cells(1, 4) sheets(h2).cells(1, 7).value = sheets(h1).cells(1, 5) sheets(h2).cells(1, 8).value = sheets(h1).cells(1, 6) row = 2 activesheet.usedrange.rows.count col = 7 activesheet.usedrange.columns.count sheets(h2).cells(x, 1).value = sheets(h1).cells(row, 1).value sheets(h2).cells(x, 2).value = sheets(h1).cells(1, col).value sheets(h2).cells(x, 3).value = sheets(h1).cells(row, col).value sheets(h2).cells(x, 4).value = sheets(h1).cells(row, 2).value sheets(h2).cells(x, 5).value = sheets(h1).cells(row, 3).value sheets(h2).cells(x, 6).value = sheets(h1).cells(row, 4).value sheets(h2).cells(x, 7).value = sheets(h1).cells(row, 5).value sheets(h2).cells(x, 8).value = sheets(h1).cells(row, 6).value x = x + 1 next next end sub

sheet1:

sheet2:

a short versión:

sub example() dim row long dim col long dim x long set sh1 = thisworkbook.worksheets("sheet1") set sh2 = thisworkbook.worksheets("sheet2") sh1.select 'headers sheet2 sh2.cells(1, 1).value = sh1.cells(1, 1) sh2.cells(1, 2).value = "specie" sh2.cells(1, 3).value = "count" = 4 8 sh2.cells(1, i).value = sh1.cells(1, - 2) next x = 2 'starting row of sheet2. row = 2 activesheet.usedrange.rows.count col = 7 activesheet.usedrange.columns.count sh2.cells(x, 1).value = sh1.cells(row, 1).value sh2.cells(x, 2).value = sh1.cells(1, col).value sh2.cells(x, 3).value = sh1.cells(row, col).value = 4 8 sh2.cells(x, i).value = sh1.cells(row, - 2).value next x = x + 1 next next sh2.select end sub

excel vba excel-vba rows