Trending September 2023 # How To Delete Blank Rows In Excel? (5 Easy Ways) # Suggested October 2023 # Top 15 Popular

You are reading the article How To Delete Blank Rows In Excel? (5 Easy Ways) updated in September 2023 on the website Nhunghuounewzealand.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Delete Blank Rows In Excel? (5 Easy Ways)

While working with large datasets in Excel, you may need to clean the data to use it further.

One common data cleaning step is to delete blank rows from your data in Excel.

In this tutorial, I will show you how to remove blank rows in Excel using different methods.

While there is no in-built feature in Excel to do this, it can quickly be done using simple formula techniques or using features such as Power Query or Go-To Special.

And for VBA aficionados, I’ll also give you a simple VBA code that you can use to quickly remove all blank rows from your data set in Excel.

One of the easiest ways to quickly remove blank rows is by sorting your data set so that all the blank rows are stacked together.

Once all the empty rows are together, you can manually select and delete them in one go.

However, you cannot simply apply the sorting on your existing dataset (as it can alter your data set by rearranging the rows while sorting). We will need to add a helper column which we will use to sort the data and then delete the blank rows.

Let me show you how it works using a simple example.

Below I have a data set where I have some blank rows that I want to remove from this data set:

Here are the steps to remove the blank columns using a helper column and sort functionality:

Now enter the below formula in cell A1, and then copy this for all the cells in the column

This above formula would give us the result “Blank” when the row is empty and the result “Not Blank” when the row is not empty.

Select the entire dataset (including the helper column)

In the Sort dialog box that opens, unchecked the option ‘My data has Headers’

Open the Sort By drop-down and select Column A (which is our helper column)

Keep the ‘Sort On’ and ‘Order’ values as is

The above steps would sort your data set so that all the blank rows are stacked up together at the top, and the remaining data set is below the blank rows.

Once done, feel free to remove the helper column

Note: When we sort our data set using the steps above, it will not mess with the original order of the rows. It will only bring all the blank rows at the top while keeping your original data set intact.

For this method to work, every cell in the blank row needs to be actually blank. If it has a space character or null string, it would not be considered blank.

Another smart way to quickly delete blank rows from your data set is by using the Find and Replace functionality.

Let me show you how it works with an example.

Below have a data set where I have some blank crows that I want to delete:

Here are the steps to do this using a helper column with Find and Replace functionality:

Now enter the below formula in cell A1, and then copy this for all the cells in the column

This above formula would give us the result “Blank” when the row is empty and the result “Not Blank” when the row is not empty.

Select the helper column (not the entire dataset).

In the Find and Replace dialog box, enter ‘Blank’ in the Find what field

Check the option – Match entire cell contents

In the Look in drop-down, select Values.

This will find all the cells that have the value blank in them and show the list below the find and replace dialog box.

Hold the Control key and Press the A key once. This will select all the cells that have been found by the Find and Replace option.

In the Delete dialog box that opens up, select the Entire row option

Once done, feel free to remove the helper column.

For this method to work, every cell in the blank row needs to be actually blank. If it has a space character or null string, it would not be considered blank.

Let me also show you how to remove blank rows in Excel by using the Go-to special technique.

However, let me warn you that there is a possibility that this may end up deleting some of the rows that may not be completely blank (and may only have a few cells that are blank).

I recommend you do not use this method with large data sets.

Below I have a data set where I have some blank rows that I want to remove:

Here are the steps to do this using the Go To Special technique:

Select the entire data set

Press F5 on your keyboard to open the Go To dialog box.

The above steps would select all the cells that are blank in the data set. Since all the cells in a blank row would be empty, this would end up selecting all the blank rows.

In the Delete dialog box that opens, select the Entire row option

The above steps would remove all the rows that contain blank cells.

CAUTION: This method should only be used if you are sure that there are no blank cells in your data set except the ones that are in the blank rows. In case there are blank cells in an otherwise non-blank row, even these rows would be deleted, as this method works by selecting the blank cells and then deleting the entire row of that blank cell.

If you need to delete blank rows often, you can also consider using a simple VBA macro code to do this.

Even if you are an absolute beginner with Excel VBA macros, don’t worry. I will show you how to set it up properly so that you can use it again and again.

But let me first give you the VBA code.

Below is the VBA code that will go through your entire data set and delete all the blank rows:

Sub DeleteBlankRows()

Dim EntireRow As Range On Error Resume Next MsgBox Selection.Row.Count

Application.ScreenUpdating = False

For i = Selection.Rows.Count To 1 Step -1 Set EntireRow = Selection.Cells(i, 1).EntireRow If Application.WorksheetFunction.CountA(EntireRow) = 0 Then EntireRow.Delete End If Next

Application.ScreenUpdating = True

End Sub

The above VBA code uses a simple For Next loop to go through each row in your data set and check whether the row is empty or not using the COUNTA function.

As soon as it finds an empty row, it deletes it and moves to the next one.

Now let me show you the steps on how to set up this VBA code to use it in Excel:

Select the dataset that has the blank rows that you want to remove

Excel Tip: You can also use the keyboard shortcut ALT + F11 to open the VB editor

Copy and paste the VBA code I’ve given above in the module code window.

Close the VB Editor

The above steps would remove all the blank rows from your data set.

Here are a few things you need to know when using this macro in Excel:

Once you have added the VBA code to your Excel file, you need to save your Excel file as a Macro-enabled file (with a .XLSM extension)

If you follow the steps above to insert a module and then add this code to the module, it is only going to work in the workbook where it has been added.

If you want this code to work on all your Excel workbooks, you should save this in your personal macro workbook. Once the code has been saved in the Personal Macro Workbook, you can use it in any Excel workbook on your system.

Note: Remember that any changes done through a VBA code are irreversible, and you will not be able to get your original data back after you have run the macro code. So it’s always a good idea to make a backup copy of your data just in case you need it in the future.

Another really quick way to remove blank rows from a dataset Excel is by using Power Query.

Let me show you how it works.

Below I have the same data set where I have some blank rows that I want to remove.

Here are the steps to delete blank rows using Power Query:

In the Create Table dialog box, make sure that the range is correct and the ‘My Table has headers’ option is checked.

Select any cell in the Excel Table

The above steps would insert a new worksheet in your workbook where the resulting table would be inserted.

One huge benefit of using Power Query is that when you have set the process once, you can reuse this query again and again.

When you refresh the query, it repeats the same steps in the back end, where it goes back to the original table, checks the data, removes the blank rows, and updates the resulting table in a few seconds.

In this tutorial, I showed you five different ways to delete blank rows from your data set in Excel.

The easiest would be to use a helper column and then and then either use the sort functionality to stack all the blank rows together and delete them, or use Find and Replace to find all the blank rows and delete them manually.

Another easy and popular way to remove blank rows is by using the Go To Special technique. However, you should use it cautiously as it can also end up deleting those rows that are not completely empty.

If you’re comfortable using VBA, you can also use a simple macro code I have given in this article to quickly all the blank rows.

And finally, I have also covered how to do this using Power Query.