Moving DataSolved/Closed

Question

Is there any one who can kindly come to my rescue!! 
I have been presented with date in the form:
A
B
C
D
E
F
G
H
I
J
?
My aim is to  present it the form: 

A	C	E	G	I	?

B	D	F	H	J

venkat1926 · Answer

try this macro 

Sub test()
Dim r As Range, c As Range
Dim dest As Range
Set r = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In r
If c.Row Mod 2 = 1 Then
c.Copy
Set dest = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
dest.PasteSpecial
End If
Next c
For Each c In r
If c.Row Mod 2 = 0 Then
c.Copy
Set dest = Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1)
dest.PasteSpecial
End If
Next c


End Sub

venkat1926 · Answer

take this line

Set r = Range(Range("A1"), Range("A1").End(xlDown))

r is defined as range from A1 to down

so you have type the first cell of the new column

for example you are having the data in column E
the line should be

Set r = Range(Range("E1"), Range("E1").End(xlDown))

this can also be even from E5 down in that case you write E5 in both cases above. 
provided this does not come into clash with other statements

see again the statement 
Set dest = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
what do you do here 
take cells(1.columns.count)
that means last cell in row 1
that is IV1.

so if your range starts from E5 and not for E1 there may be problem

when you write or modify a macro put lot of msgbox so that when you debug(that is hitting F8 successively ) you will know the mistake

Moving Data

2 responses

Similar discussions

Subscribe To Our Newsletter!