Chuyển đổi nhiều hàng cột thành 1 cột

 Transpose/Convert Columns And Rows Into Single Column With Formula

Amazing! Using Tabs In Excel Like Firefox, Chrome, Internet Explore 10!

---

The following long formula can help you quickly transpose a range of data into a column, please do as this:

1. First, define a range name for your range of data, select the range data that you want to convert, right click and choose Define Name form the context menu. In the New Name dialog box, enter the range name you want. Then click OK. See screenshot:

2. After specify the range name, then click a blank cell, in this example, I will click cell E1, and then input this formula: =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1).

Note: MyData is the range name of the selected data, you can change it as you need.

3. Then drag the formula down to the cell until the error information is displayed. All the data in the range has been transposed into a single column. See screenshot:

---

 Transpose/Convert Columns And Rows Into Single Column With VBA Code

With the following VBA code, you can also join the multiple columns and rows into a single column.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

Sub ConvertRangeToColumn() 'Updateby20131126 Dim Range1 As Range, Range2 As Range, Rng As Range Dim rowIndex As Integer xTitleId = "KutoolsforExcel" Set Range1 = Application.Selection Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8) rowIndex = 0 Application.ScreenUpdating = False For Each Rng In Range1.Rows     Rng.Copy     Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True     rowIndex = rowIndex + Rng.Columns.Count Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub

3. Press F5 key to run the code, and a dialog is displayed for you to select a range to convert. See screenshot:

4. Then click Ok, and another dialog is displayed to select a singel cell to put out the result, see screenshot:

5. And click Ok, then the cell contents of the range are converted to a list of a column, see screenshot:

-reference source-

https://www.extendoffice.com/documents/excel/1172-excel-transpose-multiple-columns-into-one-column.html?page_comment=7

Thanks!

Bỏ merge và điền full chỗ trống - Unmerge cells and fill with duplicate data with VBA code - Unmerge Quickly

 Unmerge cells and fill with duplicate data with VBA code

With the following VBA code, you can quickly unmerge the cells and fill down the values.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Modulewindow.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Sub UnMergeSameCell() 'Upadateby20131127 Dim Rng As Range, xCell As Range xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False Application.DisplayAlerts = False For Each Rng In WorkRng     If Rng.MergeCells Then         With Rng.MergeArea             .UnMerge             .Formula = Rng.Formula         End With     End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

3. Then press the F5 key to run this code, a dialog is displayed for selecting a range to work with, see screenshot:

4. Click OK, then the merged cells have been unmerged and auto-filled down the original merged values.

https://www.extendoffice.com/documents/excel/1139-excel-unmerge-cells-and-fill.html