cut the cells to 3 columns in excel & remaining columns should be added to the next row

Hi All,

I have an Excel sheet with the following columns and data below:


Adilabad WAVE 3 Agra WAVE 3 Ahmedabad WAVE 3
89 28 97 32 155 56
100 100 100 100 100 100
79 32 98 100 0 0
97 100 0 0 0 0
98 93 0 0 0 0
65 75 2 0 0 0
100 100 100 100 99 98


I have such 40 columns.My requirement is the sheet has to select only 2 columns and then the next coulmns have to be added on to the next rows and so on...
The point is only 2 columns.
If I use TRANSPOSE fuction it swaps the data aswell.But I don't want that...

Below is the output required:

Adilabad WAVE 3
89 28
100 100
79 32
97 100
98 93
65 75
100 100
Agra WAVE 3
97 32
100 100
98 100
0 0
0 0
2 0
100 100
Ahmedabad WAVE 3
155 56
100 100
0 0
0 0
0 0
0 0
99 98


Could anyone please help me on this.

Use this

Sub abc()
Application.ScreenUpdating = False
Range("A1").Select
With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
For i = 3 To LastCol - 1
With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With ActiveSheet
        LastRowN = .Cells(.Rows.Count, i).End(xlUp).Row
End With
Cells(LastRow + 2, 1).Select
Range(Cells(1, i), Cells(LastRowN, i + 1)).Select
Selection.Cut
Cells(LastRow + 2, 1).Select
ActiveSheet.Paste
Range("A1").Select
Application.ScreenUpdating = True
i = i + 1
Next i
End Sub

Hi Abdul,

Thank You for the reply.
Could you please clarify where shall I copy this code ???

1.Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
2.Right-click on the Sheet1(Sheet1), then click view code and paste the code
3.Press F5

Hi Abdul,

Thank you.
Its working fine.But for 2 columns......
Its not working for 3 columns...

In the first post you mentioned that only for 2 columns...
Will your data change each time?...So that I can modify the code...


Abdul-

Sorry Abdul,
That was just an example....I thought of modifying the code according to my specifications.
Unfortunately it has thrown some errors when I tried to modify the code.

Actually it was for 3 columns...

Use this...U can use this for any no.of columns

Sub abc()
Application.ScreenUpdating = False
Range("A1").Select
inp = InputBox("Enter no. of columns you need to fetch")
With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
For i = inp + 1 To LastCol - 1
With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With ActiveSheet
        LastRowN = .Cells(.Rows.Count, i).End(xlUp).Row
End With
Range(Cells(1, i), Cells(LastRowN, i + inp - 1)).Select
Selection.Cut
Cells(LastRow + 2, 1).Select
ActiveSheet.Paste
Range("A1").Select
Application.ScreenUpdating = True
i = i + inp - 1
Next i
End Sub

Its working.......
U r genious.....

Thank you so much......

...You are most welcome...Please close this thread as SOLVED...


Abdul-