convert data row to column after some interval



i have large amount of data in row wise. so, i want to write in column wise after some interval.

(like i have write 1000 data in row wise and after 10 interval it write in row wise)



Selected Answer

Use this macro:

Sub transpose_interval()

dest_start_col = 3
dest_cur_col = dest_start_col
dest_start_row = 1
dest_cur_row = dest_start_row

first_row = 1
first_col = 1
last_row = Range("A" & Rows.Count).End(xlUp).Row

For cur_row = first_row To last_row

    Cells(dest_cur_row, dest_cur_col).Value = Cells(cur_row, first_col).Value

    dest_cur_col = dest_cur_col + 1

    If (cur_row - (first_row - 1)) Mod 10 = 0 Then

        dest_cur_col = dest_start_col

        dest_cur_row = dest_cur_row + 1

    End If


End Sub

Change this:

dest_start_col = 3  the column where you want the data to go (the starting column)

dest_start_row = 1  the row where you want the new data to go (the starting row)

first_row = 1  the starting row of the data that you want to move.

first_col = 1  the column of the data that you want to move.

This should do the trick!



thankyou for helping,but it not do with all data(i have attached the file) and data repeating data from 31 data.
ravi1097 (rep: 4) Aug 3, '16 at 12:59 pm
I updated the macro with two little changes, try it now.
don (rep: 1282) Aug 3, '16 at 1:46 pm
thanks it workes.
ravi1097 (rep: 4) Aug 3, '16 at 2:19 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login