![]() With the above methods, you can easily reverse the data order in a column or table. How to flip data in Excel preserving formatting and formulas To save the macro, be sure to save your file as an Excel macro-enabled workbook. You select one or more columns using the mouse, not including the column headers, click OK and get the result in a moment. The Flip Columns dialog pops up prompting you to select a range to flip:.Click Insert > Module, and paste the above code in the Code window.Open the Microsoft Visual Basic for Applications window ( Alt + F11). ![]() 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |