Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Move data on alternate row to column by using Macro

0

Dear Sirs

I have a column with a dynamic row list of data as follows  

  • A 1

         A2 Explanation of A 1

  • A3

          A4 Explanation of A3

  • A5

         A6 Explanation of A5

I want to move all the Explanation data in A2, A4,and A6 an so on to the next column on the same row by using macro. After moving to Column B delete all original explanation row

Step 1 Move

  • A2 to B1
  • A4 to B3
  • A6 to B5
  • etc

Step 2  Delete original explanation rows

Delete A2,A4,A6 etc

Looking forward to having your further help in this regards

Best regards

Arsil Hadjar

excelmacros-vba
MS Office Version: 2010
posted by: Arsil (rep: 32) on Mon Jul 22, 2019 at 2:53 am
Answer
Discuss

Answers

0
Selected Answer

Hello Arsil,

Please try the formula below instead of code.

=INDIRECT(ADDRESS(ROW()*2-MOD(COLUMN(),2),1,4,1,"Sheet2"))

Paste the formula in any cell of a blank sheet and copy it one column to the right and down as far as necessary. The result will be a list with every other row moved to the second column.

posted by: Variatus (rep: 4889) on Mon Jul 22, 2019 at 4:03 am
Discuss

Discussion


Dear Variatus,

Many thanks for the response and the formula
It did create a list of the data in alternate rows to another column.

But I miss to mentioned that I want to keep the data in first row and the alternate row in the same sheet and next to each other.
So the data wiil be as follows

Original Data     Modified Data
Column A     Column A Column B
A1                 A1                A2
A2                
A3                 A3                A4
A4    
A5                A5                 A6
A6    
A7                A7                 A8
A8    
A9               A9                 A10
A10            
A11            A11                A12
A12    
After moving the data in alternate row to the next column, I need to delete the original alternate row.(Delete row A2,A4,A6,A8,A10 etc)

Modified Data
Column A    Column B
A1                    A2
A3                    A4
A5                   A6
A7                  A8
A9                  A10
A11                A12
etc
Looking for ard to having your further help in this regards
Note : (Should I create new questions for this ?)

Apologies for the inconveniences caused

Best regards
Arsil Hadjar
Arsil (rep: 32) Jul 22, '19 at 7:57 am
The solution I offered will produce the list A1, A2 / A3, A4 / A5, A6 / etc.Perhaps I should have mentioned that, after creating the list, you should remove the formulas with Copy/Paste Special -> Values. After that you can delete the original entirely. There should be no need to modify it because all you want is in the new list.
I think the current question is how to create that list. If my solution produces it correctly this thread should be closed (but not before).
Variatus (rep: 4889) Jul 23, '19 at 12:48 am
Add to Discussion


Answer the Question

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