Web Development

General discussion


Transpose rows into columns & viceversa?

By r_nair83 ·
I want to transpose rows into columns and vice versa in excel sheet using vb.net.And also the values should be sorted. For Eg
Old Format: A1 B1 C1

New Format should be :A1 B1 C1
E00 E25 E15

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by The Terminated In reply to Transpose rows into colum ...

I need some clarification. 1) You say the values should be sorted. The new format doesn't look sorted. 2) Does it have to be VB.NET? You can do it all in Excel. Select the cells, copy, Paste Special with the Transpose option. Click Data, Sort, and choose the columns to sort on and the sort order.

Collapse -

by troutusa64 In reply to Transpose rows into colum ...

Sort the values in the column first. Then Segregate the Row/Column info from the address for each source cell. Use the ASCII value of the column address (and some constant since "A" isn't "1") to turn the column letter into a number.) Designate the target cell by: Range("A1").offset(ASCII +/- constant,SourceCell.row). The column/row values are transposed. Copy the values from source to target cells. If the target row is not row one, the formula for determining the target row has to take into account the number of rows before teh target row when calculating the offset distance.

Related Discussions

Related Forums