Wednesday, September 29, 2010

Move multiple rows to Coulmns

Problem:

I took data from field which are like this

18-11

16-11

4-11

31-13

1-6

7-10

34-11

5-16

3

_

3

_

3

3

1

2

26-16

4-14

26-1

18-9

27-15

15-7

28-13

26-14

3

3

3

5

7

1

5

1

but I want convert it to

18-11 3
16-11 _
4-11 3
31-13 _
1-6 3
7-10 3
34-11 1
5-16 2
26-16 3
4-14 3
26-1 3
18-9 5
27-15 7
15-7 1
28-13 5
26-14 1

 

So, here is the macro

Sub alternateRowsToCol()
    Application.ScreenUpdating = False
    Dim row, col As Integer
    row = Sheets("Sheet3").UsedRange.Rows.Count
    col = Sheets("Sheet3").UsedRange.Columns.Count
    
    
    For i = 1 To row Step 2
    Sheets("Sheet3").Activate
  
    Sheets("Sheet3").Range("A" & CStr(i) & ":X" & CStr(i + 1)).Select
    ' X here is because I have 24 rows but in example just 8
    Selection.Copy
    Sheets("Sheet4").Activate
  
    Sheets("sheet4").Range("A" & CStr(24 * ((i - 1) / 2) + 1)).Select
    ' 24 here is because I have 24 rows but in example just 8
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
  
    Next i
    Application.ScreenUpdating = True
   
End Sub