Intensive phobia? Inserting a blank row into a table is not so difficult!

Usually in the production of weekly report, statistical data such as the report file, in order to facilitate data viewing and aesthetics, we often have to insert blank spaces between the rows and columns, columns, if you need to insert a lot of blank lines, manual insertion is obviously time-consuming and laborious. Is there an efficient insertion method?

1. Local use of Excel's built-in features

For the addition of blank rows and columns, some components already built in Excel can be quickly implemented. For example, when creating the word list data, in order to facilitate viewing and obscuring the recitation, it is now necessary to insert a blank line in the original word list to perform the interval. This is easily realized by using the built-in function of Excel (Fig. 1).

Figure 1 Example of inserting blank rows and columns

First, input 1 and 2 values ​​in E2 and F3, and then select the “E2:F3” array to fill in the last row of the original data, so that the data and the values ​​entered in columns E and F are staggered. Select E2:F11, press F5 to click “Targeting Conditions”, and select “Null Value” for the positioning criteria (Figure 2).

Figure 2 Selecting Targeting Conditions

Then all the blank cells of E2:F11 will be selected. Click "Start → Insert → Cell → Insert Worksheet Row". Excel will insert a blank row at each row interval, and finally delete the E, F auxiliary columns. Can (see Figure 3).

Figure 3 Insert worksheet row

hint:

For files that require data statistics or sorting, inserting a blank line may affect the operation. If you want to delete blank lines in batches, you can also use the F5 null positioning method described above to locate all null values ​​and select “Delete”.

The above method is to add the insert blank line, if you want to insert a blank column, as above, enter the auxiliary data in the last line of the original data, such as this case is A12 → D12, in turn input 1,2,3,4, E12 → H2 input 1.1, 2.1 , 3.1, 4.1 (Figure 4).

Figure 4 Entering Auxiliary Data

Click on "Data → Sort and Filter → Customize Sort", in the open sort window, click "Options → Sort by Row", the main keyword select "line 12" (that is, the custom auxiliary line), the sorting basis is "Value" The sorting method is selected as "ascending" (Figure 5).

Figure 5 sorting settings

After this sort of data is completed, a blank column will be automatically added between each column, and the auxiliary row will be deleted when prompted (Figure 6).

Figure 6 Add blank column

Through the above operations, you can see that the blank rows and columns are added mainly through the method of adding auxiliary rows and columns. The adding of rows is done by adding staggered auxiliary data at the end of the column of the original data, and then by sorting, if you want to insert multiple rows. Then stagger the corresponding line data. The blank column adds the auxiliary row data corresponding to the column number at the end of the original data. Everyone can complete the addition of blank ranks according to the actual situation of their own data.

2. One-step VBA script to quickly add ranks

Although the above-mentioned method of adding auxiliary ranks is simple, it is still a little cumbersome for many friends who pursue efficiency. For this type of friend, you can also use VBA scripts to add a single key to a blank row.

To insert a blank column in the above example, click on "Development Tools → VB → Insert → Module". In the window that opens, paste the following code into the blank space. This saves a macro script named "Insert Blank Column". (Figure 7).

Sub inserts a blank column ()

For i = 1 To 3

Cells(1, 2 * i).Select

Selection.EntireColumn.Insert

Next i

End Sub

Code explanation:

For i = 1 To 3 : This example shows only inserting 3 columns of blank columns. Please select the specific values ​​according to your actual data.

Cells(1, 2 * i).Select: means to insert a blank column at the first row, 2*i columns

Selection.EntireColumn.Insert: EntireColumn indicates that the inserted column, if it is inserted row use EntireRow code.

Figure 7 Insert blank column code

After saving, return to the original data window, click "Development Tools → Macro → Insert Blank Column → Execute". After executing this macro, a blank column will be inserted between the original columns (Figure 8).

Figure 8 Running a macro to insert a blank column

If you are inserting a blank line, enter the following code:

Sub inserts a blank line ()

For i = 1 To 10 to insert 10 blank lines

Cells(2 * i, 1).Select

Selection.EntireRow.Insert , indicating that an empty row was inserted

Next i

End Sub