how to organize the data ?

hi
i'm working on excel 2003
i have a sheet with 4 columns (from column b) , and 2000-3000 rows
i want to put all the data in column a in this way : b1 in a1 , c1 in a2 , d1 in a3 , e1 in a4 , b2 in a5 , c2 in a6 etc ...

any idea ?

use copy->paste special->transpose

rgds

johnjohns

Sorry, this should work

Sub DoIt()
Dim OneRow As Range
ActiveSheet.Range(Range("B1"), Range("E" & Rows.Count).End(xlUp)).Select
For Each OneRow In Selection.Rows
  OneRow.Copy
  ActiveSheet.Range("A" & Rows.Count).End(xlUp).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
   
Next OneRow
End Sub

rgds

johnjohns

Got it DonkeyOte! I should have used 'could' as there is still a mistake in my answer. My apologies, I was in a hurry (although that is not an excuse). Hope this may work

Sub DoIt()
Dim OneRow As Range, TheCount As Long
ActiveSheet.Range(Range("B1"), Range("E" & Rows.Count).End(xlUp)).Select
For Each OneRow In Selection.Rows
  OneRow.Copy
  ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(IIf(TheCount = 0, 0, 1), 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
  TheCount = TheCount + 1
Next OneRow
End Sub

rgds

johnjohns

Thanks
its work great !!!

hi
i have more question : i dont know how many rows/columns i have.
how can i do this with something like ctrl+end ?
can i put this in the code / formula ?

many thanks
eran

replace the second line with this and try

ActiveSheet.Range(Range("B1"), Range("B" & Rows.Count).End(xlUp).End(xlToRight)).Select

rgds

johnjohns

thank you very much
is there a way also in the formula (with the "index") ?

hi
i didnt understood what you mean : counta*

eran

Thank you
i will chack it

thank tou again