How to remove empty rows in Excel. 4 options for deleting empty rows in an Excel spreadsheet

При составлении таблиц в Microsoft Excel и работе с формулами часто образуются пустые строки, которые проблематично убрать. В данной статье будут описаны самые распространенные методы их удаления.

How NOT to Delete Blank Rows in Excel

Removing empty lines is not recommended through the Find and Select section. Данная команда удаляет сразу всю строку, в которой есть пустоты. В таком случае ячейки с нужной информацией также пропадут. The “Find and Select” tool is relevant when an entire row in the table has become empty, and it needs to be deleted. In such a situation, the following steps must be taken:

1. Select an empty line with the mouse.
2. Go to the “Find and select” section.
3. Click on “Go to Special”.
4. Press LMB on the word “Space”. After that, the void should disappear, and the table will be transformed.

In Excel, empty rows can be removed by sorting the table or setting a filter. However, this method can also lead to the complete removal of a row with filled cells. Moreover, adjacent rows or columns may also suffer.

Pay attention! In Excel, you cannot delete blank rows by selecting individual cells in a table to avoid losing important information. It is better to use other methods, which will be described later.

Формула для удаления пустых строк в Excel

Для выполнения поставленной задачи можно воспользоваться специальной формулой, которая работает во всех версиях программы: =IF(COUNT(A2:E2)=0,”Empty”,””). А2 и Е2 — это первая и последняя ячейки строки, в которой необходимо убрать пустоты. Аналогичным образом выражение применяется для любого столбца таблицы. Пользователю необходимо поставить курсор мыши в свободную ячейку и прописать данную формулу в строке сверху главного меню. Далее эту формулу потребуется растянуть для всех строк или столбцов таблицы, зажав ЛКМ крестик в правом нижнем углу первой ячейки. После таких манипуляций пустые строчки пропадут.

How to remove empty rows in Excel using Power Query

Power Query — это специальный инструмент, который присутствует в версиях Excel, начиная с 2013 года.

Important! Such a tool changes the formatting of the table, the filling of cells, their size, orientation.

To delete empty cells in tables, you need to follow a few simple algorithm steps:

1. Use the manipulator to select a line or column in which there are voids that require uninstallation.
2. In the Excel interface at the top, go to the “Data” section.
3. В графе открывшихся параметров кликнуть по кнопке «Получить данные».
4. In the context menu, click on the line “From other sources” and then select the option “From table/range”. Now a small menu will open where you need to check the box next to the “Table with title” option and click on “OK”.

5. Explore the interface of the Power Query editor that opens.
6. Click LMB on the “Delete Rows” button at the top of the section.
7. In the context menu, click on “Delete empty lines”.

8. Click the “Close and Download” button.
9. Check result. After doing the above manipulations, the editor window should close, and empty cells will disappear in the table.

Additional Information! After implementing the considered method, the format for displaying dates and numbers in the table will change.

Как удалить строки, если ячейка в определенном столбце пуста

After construction, single empty cells may appear in some columns of the table. It is easy to get rid of them using the gap method discussed at the beginning of the article. In this situation, this method of uninstallation can be applied. You need to act as follows:

1. Нажать ПКМ по названию столбика, в котором есть пустоты.
2. In the context menu, click on “Select” and in the next window select “Empty cells”. After closing the window in the selected column, only voids will be highlighted, and the cells with information will remain unaffected.
3. Right-click on any selected cell and click on the “Delete” line. A small window will open in which you need to put a toggle switch opposite the “Strings” field, and then click “OK”.
4. Check result. Rows that contained empty cells should be completely uninstalled from the table. As mentioned above, the minus of the method is that the cells with information will also be deleted.

How to remove extra rows under the data or hide them

In the process of filling out a table in Excel, empty lines often appear, in which, in fact, hidden information is written. Extra cells can be removed as follows:

1. Select an empty cell LMB and simultaneously hold down the “Ctrl + Shift + End” buttons. This command will select the remaining cells in which something is actually written.
2. Right-click on the cell and select the “Delete” option in the context type box.
3. Check if the extra voids are removed. Selected cells with hidden data should be uninstalled as in the previous method.

Sometimes the user does not want to delete information from hidden table cells. In this case, they can be hidden. Hiding lines in Microsoft Office Excel is carried out according to the following instructions:

1. Select the cell below the last row with data and hold down “Ctrl + Shift + End” to select the remaining extra spaces.
2. After selecting all the voids for any of them, right-click and click on the “Hide” option.
3. Можно зажать кнопки «Ctrl+9» для скрытия лишних ячеек.
4. Hold “Ctrl + Shift + 9” if you need to make the voids visible again.

How to quickly remove blank rows in Excel using VBA

VBA is a special programming language designed to automate processes in Microsoft Office editors. With it, you can quickly delete empty cells in Excel by copying the desired program code. To implement this method of uninstallation, it is not necessary to be a programmer. Next, we will consider several commands implemented in VBA in Excel.

Macro 1. Delete empty lines in the selected range

If you select the desired lines and enter this code, you can remove all the voids in the specified range. In this case, the user will not see any warnings, messages, pop-up dialogs, etc. You can see the code for removing voids in a range in the following image:

Чтобы ввести макрос в Excel на языке программирования VBA и запустить его, необходимо:

1. Switch to the “Developer” section, located in the list of options at the top of the main menu.
2. Click the “Record Macro” button.
3. Copy the code from the image above and enter it in the appropriate field.
4. Save changes by clicking on “OK”.
5. Для запуска программы необходимо перейти во вкладку «Макросы», выбрать нужный код по названию созданных, выделить диапазон, в котором предстоит удалить пустоты, и кликнуть «ОК».

Important! The created macro will be saved in Excel, and you will not need to enter it every time to perform a specific action.

Макрос 2. Удалить все пустые строки в Excel

С помощью написанного кода можно деинсталлировать сразу все пустоты, которые находятся на активном листе Эксель. Для этого используется следующий код:

Macro 3. Delete row if cell is empty

This is the whitespace method that was described above, implemented using code. This method allows you to delete the entire line in which there is one or more empty cells, as well as get rid of extra lines. The presented code will look like this:

Before running any code in Excel, it’s a good idea to back up your work to avoid losing important information if you make a mistake.

How to add a macro to your book

Для записи кодов в Excel создается своя книга. Выше был рассмотрен альтернативный метод создания и запуска макросов. Есть еще один способ, который заключается в выполнении следующих шагов:

1. Open the sheet in which you want to delete any cells.
2. Press Alt+F11 to launch the built-in Visual Basic editor.
3. In the parameters column on the left of the window that opens, click on the word “Insert”, and then go to the “Module” tab.
4. Paste the desired program code into the displayed window.
5. Click F5 to check if the code works.
6. Click on the “Save” button to add the macro to your workbook.

Additional Information! To activate the created macro next time, you need to go into the book, select the appropriate table, hold down “Alt + F8” from the keyboard, select the desired code and click on the “Run” button.

How to remove empty rows in Excel using sort

Sorting data in a table helps to remove gaps. This operation is performed according to the algorithm:

1. Select the desired range of LMB cells.
2. Перейти в раздел «Данные» сверху главного меню программы и кликнуть по кнопке «Сортировка».

3. In the next menu, select a column to sort the information in it and click “OK”.

4. Make sure the table is sorted. Blank lines will be placed below the table. They can be removed if necessary.

How to remove empty rows in Excel using filtering

Процесс выполнения поставленной задачи подразделяется на следующие этапы:

1. Similarly, select the table and switch to the “Data” section at the top of the program window.
2. Now, instead of sorting, click on the “Filter” button.

3. In the window that appears, check the box next to the value “(Empty)”.

4. Check that all empty cells in the table are highlighted and delete them using the standard method discussed above.

Перед выполнением способа фильтрации данных понадобится выделить весь диапазон таблицы, иначе фильтрация будет осуществляться только до первой пустоты.

Pay attention! To uninstall empty cells in the way discussed above, the user will need to carefully filter each column in the table so as not to delete the necessary information.

How to remove empty rows in Excel by selecting a group of cells

Excel has a built-in tool with which you can select cells in groups. With its help, voids are removed according to the algorithm:

1. Select the entire LMB table.
2. Click on the magnifying glass icon in the tab bar at the top of the program window. This is the Find and Select menu.
3. In the context menu, click on the line “Select a group of cells …”.

4. In the window that appears, put the toggle switch in the “Empty cells” field and click on “OK”. All spaces in the table will be highlighted.

5. Удалить строку методом пробелов и проверить результат.

How to remove empty rows in excel using a helper column

To uninstall voids next to the table, you can create a column that will act as an auxiliary element. This is one of the fastest and most reliable removal methods. To implement this method, you need to perform a few simple manipulations according to step-by-step instructions:

1. Create another column to the right of the original table and name it, for example, “Blank line” so as not to get confused in the future.
2. Write the formula “Count voids”, specifying the appropriate range in which the program will check for empty lines.
3. Когда пустые строчки будут обнаружены, их останется только выделить и деинсталлировать.

Important! This formula looks like this: “=COUNTINGBLANK(A6:D6)”. Instead of letters in brackets, a range of cells is indicated.

Conclusion

Таким образом, существует несколько способов удаления пустых строк в Microsoft Excel, каждый из них актуален для конкретного случая. Чтобы разбираться в теме, необходимо внимательно ознакомиться с вышеизложенной информацией.