copy vertical data and paste it horizontally in excel

#COPY VERTICAL DATA AND PASTE IT HORIZONTALLY IN EXCEL HOW TO#

Click Insert > Module, and paste the above code in the Code window.Open the Microsoft Visual Basic for Applications window ( Alt + F11).

#COPY VERTICAL DATA AND PASTE IT HORIZONTALLY IN EXCEL HOW TO#

Set WorkRng = Application.InputBox( 'Range', xTitleId, WorkRng.Address, Type:=8)Īpplication.Calculation = xlCalculationManualĪpplication.Calculation = xlCalculationAutomaticĮnd Sub How to use the Flip Columns macro XTitleId = 'Flip columns vertically' Set WorkRng = Application.Selection In other words, ROWS creates a kind of decrementing counter for INDEX so that it moves from the last item toward the first item.ĭim Arr As Variant Dim i As Integer, j As Integer, k As Integer On Error Resume Next In the second cell (B3), the relative reference A2 changes to A3, consequently ROWS(A3:$A$7) returns 5, forcing INDEX to fetch the second to last item.For the first cell (B2), ROWS(A2:$A$7) returns 6, so INDEX gets the last item in the list (the 6 th item).In our formula, it's the clever use of the relative and absolute references that does the 'flip column' trick: In its simplest form, ROWS(array) returns the number of rows in array. The row number is worked out by the ROWS function. In the array, you feed the entire list you want to flip (A2:A7 in this example). For our sample data set, the formula goes as follows:Īt the heart of the formula is the INDEX(array, row_num, ) function, which returns the value of an element in array based on the row and/or column numbers you specify.

Copy vertical data and paste it horizontally in excel