How to create list from range in Excel?
You may need to create a list by a range of data frequently, for instance, a range of data on 24 solar terms as shown as below. Of course, you can copy and paste them into a list manually, but it seems somewhat tedious if there are many columns in the range. Actually, there are a couple of tricks to create a list from specified range in Microsoft Excel easily.
In Excel, you also can use VBA code to quickly import a list or range.
1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.
2. Click Insert > Module, and copy the VBA into the module.
VBA: Create list from range
Sub UniqueList() 'Updateby20140304 Dim InputRng As Range, OutRng As Range xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Range:", xTitleId, InputRng.Address, Type:=8) Set OutRng = Application.InputBox("OutPut to (single cell):", xTitleId, Type:=8) For i = 1 To InputRng.Rows.Count For j = 1 To InputRng.Columns.Count OutRng.Value = InputRng.Cells(i, j).Value Set OutRng = OutRng.Offset(1, 0) Next Next End Sub
3. Click Run button or press F5 to run the VBA. A dialog displayed on the screen, and select the range of data that you will create a list with. See screenshot:
5. Click OK, select a cell to output the list from the pop-up dialog, then click OK. See screenshot:
Then the range of data has been created as a list in the selected cell.
The handy tool – Kutools for Excel's Transform Range function also can quickly and easily create a list from a range data.
1. Select the data range you want to create it as a list, and click Kutools > Range Converter > Transform Range. See screenshot:
2. In the pop-up Transform Range dialog, check Range to single column, and click OK, another dialog displayed for you to select a blank cell to output the list, click OK. See screenshot:
3. Then the list is created.