Results 1 to 13 of 13

Moving alternate rows of data to column

Threaded View

  1. #1
    Registered User
    Join Date
    02-20-2014
    Location
    SG
    MS-Off Ver
    Excel 2013
    Posts
    19

    Moving alternate rows of data to column

    Hi guys, Im trying to put convert an initial N x 1 data, such that, for every 2nd term, it goes to the 2nd column and the 3rd term to the third column. This goes on for the remaining data.

    e.g.

    1
    2
    3
    4
    5
    6
    7
    8
    9

    to become:

    1 2 3
    4 5 6
    7 8 9




    I have this small VBA script:

    Sub MoveRange()
    'Updateby20140730A
    Dim rng As Range
    Dim InputRng As Range, OutRng As Range
    xTitleId = "Transform 2nd and 3rd row to 2nd and 3rd column"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
    Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
    Set InputRng = InputRng.Columns(1)
    For i = 1 To InputRng.Rows.Count Step 2
        OutRng.Resize(1, 3).Value = Array(InputRng.Cells(i, 1).Value, InputRng.Cells(i + 1, 1).Value, InputRng.Cells(i + 2, 1).Value)
        Set OutRng = OutRng.Offset(1)
    Next
    End Sub
    However when running this, it gives me instead:

    1 2 3
    3 4 5
    5 6 7
    7 8 9


    Can someone let me know what is wrong with my VBA code?

    Thanks!
    Last edited by Corsa88; 09-11-2015 at 10:32 PM. Reason: Table and code format

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How Small Function works when small(array,1),small(array,2) are same ?
    By bkvenkat in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-02-2015, 02:00 AM
  2. [SOLVED] Small macro need to modify
    By nur2544 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-12-2013, 11:16 AM
  3. Functions similar to SMALL or overcome limitations of SMALL
    By arvindtechie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-05-2013, 10:59 AM
  4. Need to optimize the small macro
    By Carnifex930 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-11-2011, 09:00 PM
  5. need small help with a macro please
    By excelguru in forum Excel General
    Replies: 3
    Last Post: 11-13-2006, 12:22 AM
  6. Small Macro
    By tbobo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2006, 01:40 PM
  7. Newbee needs HELP-Small Macro
    By tbobo in forum Excel General
    Replies: 4
    Last Post: 03-08-2006, 03:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1