Excel VBA Delete Blank Or Empty Rows: 5 Easy-To-Use Macro Examples

You're probably aware of how annoying and time-consuming cleaning up data can be. Data scientists spend a substantial amount of time collecting and preparing data.

As you're probably aware, the process of cleaning up data with Excel can be automated to a certain extent using tools such as Text functions or macros.

In this tutorial, I focus on a very “dangerous”, annoying and common situation when working with data: the existence of blank rows and cells in data sets. Blank rows can generate several different problems. This includes, for example, when you import an Excel worksheet into other applications, such as Access.

The main point is: you want to be able to delete blank rows or rows that have blank cells quickly and easily. You can do this, among others, using macros.

There are several ways to create a macro that deletes blank rows or rows with blanks cells. In fact, you can find many different suggestions online.

Instead of trying to re-invent the wheel, in this tutorial I show you 5 of the most common examples of macros to delete blank rows or rows with empty cells quickly and easily. In addition to the VBA code itself, each macro is accompanied by a very specific explanation of how each application is structured and what the purpose of each statement is. These thorough explanations should help you understand what the macro is actually doing and, at the same time, improve your understanding of macros and Visual Basic for Applications.

The 5 macros that I explain and explore in this tutorial achieve the following:

• The first 3 macros delete a complete row based on whether it has empty cells. More precisely:
  • Macro #1, named Delete_Blank_Rows, deletes entire rows based on whether a cell of that row in a given range is empty.
  • Macro #2, named Delete_Blank_Rows_2, achieves the same purpose as Macro #1 but uses a different structure. Whereas Macro #1 uses the Select method and the Selection property (both of which I explain below), Macro #2 is structured around a With…End With statement (which I also explain below).
  • Macro #3, named Delete_Blank_Rows_3, deletes entire rows based on whether a cell of that row within the current selection is empty.
  • The main difference between Macro#3 vs. Macros #1 and #2 is that Delete_Blank_Rows_3 works with the cell range that you've selected immediately before executing the macro. Delete_Blank_Rows and Delete_Blank_Rows_2 work on a cell range that is fixed in the VBA code. Basically, Macros #1 and #2 work with absolute references whereas Macro #3 works with relative references.
• The last 2 macros delete rows when the entire row is empty. The main difference between Macro #4 (named Delete_Empty_Rows) and Macro #5 (named Delete_Empty_Rows_2) is the way in which they are structured.

You can use the following outline to skip to the macro (or section) that you're interested in:

Since there are several ways in which you can structure a macro to delete blank rows or rows with empty cells, at the end of this tutorial I introduce some of the ways in which you can modify any of the macros that appear here in order to be in a better position to achieve your particular objectives. Also, since I know that there are many other ways to approach the problem, I encourage you to share your knowledge and experience in the comments at the end of this post.

Let's get started by taking a look at the sample data that I use to illustrate how to delete blank rows or rows with empty cells in Excel quickly and easily by using macros…

Macros To Delete Blank Rows Or Rows With Empty Cells: Sample Data

As usual, everything that I explain in this Excel macro tutorial is illustrated with an actual example. In this case, I use stock market data.

More precisely, I used Google Finance to obtain the historical stock data for every trading day of the year 2014 of the following companies:

• Microsoft.
• Google.
• Apple.
• Amazon.

The data has been pasted in a single Excel workbook. This Excel VBA Delete Blank or Empty Rows Tutorial is accompanied by Excel workbooks containing the data and macros I use. You can get immediate free access to these example workbooks by subscribing to the Power Spreadsheets Newsletter.

The original table with all the information looks roughly as follows (this is only a partial screenshot):

As you can see, this is quite a big table:

• There are 4 companies.
• For each company, there are 252 days of data.
• For each day and company, there are 5 pieces of data: 4 stock prices (open, high, low, close) and the volume.

Imagine that you are working with this data set and find out that there may be some empty cells or empty rows and you need to delete those rows that are affected. As you can imagine, cleaning up such data set manually can be extremely tedious and there is a significant risk of making mistakes by, for example, missing some blank cells.

Fortunately, by using a relatively simple macro, you can take very large data sets and delete whole rows that have empty cells in them or that are completely empty. Let's start taking a look at these macros…

Macro #1: Delete Rows Based On Whether Any Cell In A Given Range Is Blank (Option 1)

Let's assume that you're getting ready to analyze the performance of the stocks of Microsoft, Google, Apple and Amazon during 2014. Your boss needs to get a rough idea of how these shares performed during 2014 before a meeting he has in half an hour.

You rush to your computer, open the Excel workbook that contains all the information but…

You notice that somebody in your office got to the data before you and accidentally deleted some of the closing prices of Microsoft's share price. To be precise, instead of the 252 days of data, you only have 230 days of data in this particular column.

To top it off, your Internet connection is not working properly so you won't be able to replace the data (by getting it from Google Finance) on time before your boss' meeting.

After going to the desk of the co-worker who accidentally deleted the data and yelling at him, you go back to your cubicle. You're not really sure how to explain to your boss that you won't be able to deliver the data he needs before his meeting.

But then…

You start asking some better questions. For example, you ask yourself:

• Can I clean up the data and fix the workbook so that the missing data doesn't cause problems?
• Is the missing data really that important? My boss only want to get a rough idea of the share performance during 2014. Can I provide that using only the available data? I'm not really advocating that you do this often. But in this particular case, it may work and is definitely better than providing your boss no data at all.
• Could I provide my boss an analysis that takes into consideration the days for which I have all the information? For sure. You can do this by deleting the rows for which you don't have all the information and, then, carry out your analysis.
• How can I delete the rows for which I don't have all the information? More precisely, how can I delete a full row depending on whether a cell in the column where Microsoft share's closing price appears is blank? There are several ways to achieve this goal, such as using the 3 macros that I explain below (Macro #1, Macro #2 and Macro #3 of this tutorial).

If you know the range in which you want to search for blank cells and, based on that result, delete whole rows, the macro below can be helpful.

This particular macro deletes an entire row if there are any blank cells in the range that is specified in the VBA code. In the example, you can use the macro to delete all the rows for which Microsoft share's closing price is missing.

The main statements of this particular macro are as follows:

On Error Resume Next
    Range("E6:E257").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

I name the macro “Delete_Blank_Rows”, assign the keyboard shortcut “Ctrl + Shift + D” and add a brief description and obtain the following VBA code:

Let's examine the macro line-by-line to understand the process it follows.

Line #1: On Error Resume Next

On Error statements do the following:

• Enable an error handling routine.
• Specify the location of the routine within the procedure.

The main reason to use On Error statements is to trap errors, handle them, and improve the user experience. If a run-time error happens and there is no On Error statement, the error is fatal. Execution of you macro stops and Excel displays an error message.

The full statement above is an On Error Resume Next error handler. These particular type of error handler is commonly used, among others, when you're expecting errors and know how to handle them. The On Error Resume Next error handler works by instructing Excel to continue the execution of the macro with the statement that follows that where the error occurred.

The main reason why the On Error Resume Next error handler should be included in the Delete_Blank_Rows macro is that there are, indeed, errors to be expected when executing the macro. In particular, if the range in which the macro searches for blank cells doesn't actually have any empty cells, there will be a run-time error and Excel stops executing the macro unless there is an appropriate error handling routine.

On Error statements are generally applicable to all subsequent statements in the VBA code until the statement is disabled by the On Error GoTo 0 statement. If there is no On Error GoTo 0 statement, the On Error statement is only disabled once the procedure is exited.

Line #2: Range(“#:##”).Select

Range(“#:##”) returns an object which represents a range of cells (or a single cell). “#:##” is the argument that you use to name the range.

Select activates that particular object so that, in the next step, you can use the Selection property.

In the example where you need to search for blank cells in the column where the closing prices of Microsoft's stock is, the relevant range is from cell E6 to cell E257 as shown in the 2 screenshots below. Therefore, the relevant VBA statement for the Delete_Blank_Rows macro is “Range(“E6:E257″).Select”.

Line #3: Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

This statement is slightly longer than the previous ones, so let's examine each of its elements separately.

Element #1: Selection.SpecialCells(xlCellTypeBlanks).

This is known as the Range.SpecialCells method. The purpose of the Range.SpecialCells method is to get an object (in this case a range) representing the cells that match the type and value that are set forth within the parenthesis.

To understand this definition, let's take a look at the usual syntax of the Range.SpecialCells method: “expression.SpecialCells(Type, Value)”, where:

• “expression” stands for a Range object. In this case the range object is Selection, which returns the current selection. In the Delete_Blank_Rows macro, this represents the range of cells that has been selected pursuant to the previous statement (Range(“#:##”).Select). In the Excel worksheet that contains the stock prices of Microsoft, Google, Amazon and Apple, Selection represents the range between cells E6 and E257.
• The parenthesis immediately after SpecialCells includes 2 arguments: XlCellType and value.
  • XlCellType is a required argument that specifies which type of cells should be included. The Delete_Blank_Rows macro requires that the cells to be included in the range to be returned by the method are xlCellTypeBlanks. xlCellTypeBlanks are, as implied by their name, empty cells.
  • Value is an optional argument that is usually required when the type is xlCellConstants (cells that contain constants) or xlCellTypeFormulas (cells that have formulas). The Delete_Blank_Rows macro doesn't include a value, meaning that all blank cells are returned.

When applied to the Excel worksheet with the stock data for Microsoft, Google, Amazon and Apple, this particular statement returns an object that represents all the blank cells in the range between cells E6 and E257 (the column where the closing price of Microsoft's share appears).

Element #2: EntireRow.

The EntireRow property returns an object which, as you may expect, is an entire row (or several entire rows).

The syntax of the EntireRow property is “expression.EntireRow”. In this case, “expression” must provide an object.

In the case of the Delete_Blank_Rows macro, the expression is Selection.SpecialCells(xlCellTypeBlanks). As a consequence, in the example I have been using, the EntireRow property returns all the (entire) rows that contain empty cells in the range of cells E6 to E257 (where the closing prices of Microsoft's shares are).

Element #3: Delete.

The Delete method (“Delete”) deletes an object. In the example above, the object that is deleted are the entire rows that contain empty cells in the column where Microsoft's closing price is displayed.

Summary Of Process Followed By Delete_Blank_Rows Macro

Now that I have showed you what each of the statements in the Delete_Blank_Rows macro does, you have a much better understanding of how Macro #1 works. As you probably know by now, what this macro does in a nutshell is the following:

• Step #1: The macro selects a range of cells that you've determined. In the example above, the range of cells is the column where the closing price of Microsoft's share appears or, more precisely, cells E6 to E257.
• Step #2: The macro goes through the selected range to find all the cells that are empty.
• Step #3: Whenever the macro finds an empty cell, it selects the entire row.
• Step #4: Once the macro has found all the empty cells in the relevant range and selected the entire rows where those cells are, it deletes the rows.

The macro includes an On Error Resume Next error handler for purposes of guaranteeing that the program execution doesn't stop, for example, in case the macro doesn't find any empty cells in the range that you've asked it to search in. This particular error doesn't have side effects on the execution of the macro and, therefore, can be left “unfixed”.

Graphically, the process followed by the Delete_Blank_Rows macro looks roughly as follows:

Finally, let's go back to the example where you're preparing a summary of the performance of the stocks of Microsoft, Google, Apple and Amazon during 2014 for your boss. Take a look at the image below to see how the Delete_Blank_Rows macro work in practice and, most importantly, how it can help you get your job done faster and keep your boss happy 😉. Notice how the rows with empty cells in column E (Microsoft stock's closing price), such as rows 11 and 20, are deleted.

Macro #2: Delete Rows Based On Whether Any Cell In A Given Range Is Blank (Option 2)

Let's take a look again at the VBA code behind the Delete_Blank Rows_Macro:

And allow me to ask you a question:

How would you modify the VBA code in order to avoid using the Select method and Selection property?

If you take a close look at the 2 main statements in the VBA code of the Delete_Blank_Rows macro, you may notice that both “Select” and “Selection” make reference to the same thing: the range of cells which, in this particular example, is “Range(“E6:E257″)”.

Therefore, you can use a With…End With statement. The purpose of a With…End With statement is to simplify the syntax of the macro by executing several statements, all of which refer to the same object or structure. You can find an example of a very simple With…End With statement in this Excel macro tutorial for beginners.

If you're wondering why it may be advisable to avoid using the Select method and the Selection property, I provide you some reasons at the end of this section.

In this case, the most relevant statements of the macro are the following:

On Error Resume Next
With Range("E6:E257")
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

I name the macro “Delete_Blank_Rows_2”, assign the keyboard shortcut “Ctrl + Shift + E” and add a short description of the macro does and the full VBA code looks as follows:

Let's take a look at each of the lines of code to understand what is the process followed by this macro.

Line #1: On Error Resume Next

You already know that this statement:

• Is an On Error Resume Next error handler.
• Instructs Excel to continue the execution of the macro with the statement that follows the point where a run-time error occurs.
• Has as main purpose, in the macros that have been studied in this tutorial, to avoid the macro execution stopping in case there are no blank cells in the range that you've asked the macro to search in.

Line #2. With Range (“#:##”)

The word “With” signals the beginning of a With…End With statement. In these type of statements, the word With is usually followed by an objectExpression which is, as implied by its name, an expression that evaluates to an object. In this case, the objectExpression is Range(“#:##”).

The image below shows the whole With…End With statement:

As a consequence of the above, Range(“#:##”) is the object to which each of the lines within the With…End With statement refers to. “#:##” is where you specify the actual range the statements should work with.

I continue working with the example where you're trying to fix the Excel worksheet that has the daily prices for the stocks of Microsoft, Google, Apple and Amazon during 2014 and need to delete certain rows depending on whether the closing price of Microsoft's share appears or not. In this case, you know that “#:##” should be replaced by “E6:E257”.

Line #3: .Value = .Value

This is an example of the Range.Value property in action. This property returns or sets the value for a specified range.

Since the line is located within the With…End With statement, it makes reference to the cell range between E6 and E257.

Can you see what is going on here? Why does the statement include .Value on both sides of the equal sign?

As you may imagine, the effect of this type of statement is to set the values of the range equal to their current values. In Excel, this is helpful when you're searching for empty cells or for cells that appear empty but actually have double quotes (“”). Cells that are blank because they contain quotation marks (“”) usually behave differently from cells that are completely empty.

This means that, when a macro is searching for empty cells, it doesn't look at cells that have quotation marks (“”) as being empty. Therefore, those cells aren't taken into consideration during the deletion process. The statement “.Value = .Value” converts cells that have quotation marks into actual blanks. Those blanks can then be used by the macro for purposes of determining which rows to delete.

Line #4: .SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Since this line is located inside the With…End With statement, it makes reference to the objectExpression that appears on the opening of the statement. Therefore, in this case, this line of code works with Range(“#:##”).

In other words, this line of code says “Range(“#:##”).SpecialCells(xlCellTypeBlanks).EntireRow.Delete”. This is, essentially, the same as the “Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete” that appeared in Macro #1. The only difference is in the first item: (Range(“#:##”) vs. Selection).

You already know that:

• Selection.SpecialCells(xlCellTypeBlanks) is an example of a Range.SpecialCells method. Range(“#:##”).SpecialCells(xlCellTypeBlanks) is another example of such method.
• The purpose of this method is to get an object that represents the cells that match a certain type and value, both of which appear within the parenthesis.
• In Macro #1, the effect of using this method was to get all the blank cells in the chosen range of cells, with that range of cells being represented by Selection.

Considering the above, what do you expect is the effect of Range(“#:##”).SpecialCells(xlCellTypeBlanks)?

If you answered that the effect is the same, you're right.

“Range(“#:##”).SpecialCells(xlCellTypeBlanks)” returns all the blank cells within Range(“#:##”). In the example that I have been using, this means all the blank cells in the column where the closing price of Microsoft's share appears.

Line #5. End With

This line simply terminates the With…End With statement.

Summary Of Process Followed By Delete_Blank_Rows_2 Macro

As you may expect, the process followed by this macro (Delete_Blank_Rows_2) is very similar to that used by the previous macro where the With…End With statement was not used (Delete_Blank_Rows). However, its not exactly the same process, so let's take a summarize what the Delete_Blank_Rows_2 macro actually does:

• Step #1: The macro clarifies that a series of statements work with a particular range, without the need to repeat what the range is every single time. In the example that I have been using this is the range of cells E6 to E257, where the closing prices of Microsoft's stock appear.
• Step #2: The macro takes all the cells in the range and sets their values equal to their current value. The main effect of this is that it turns cells that appear blank because they have quotation marks (“”) into actual blanks.
• Step #3: The macro goes through that particular range searching for empty cells.
• Step #4: As the macro finds blank cells, it selects the entire row where those empty cells are located.
• Step #5: The macro deletes the relevant rows.

Just as in the case of the Delete_Blank_Rows macro, the Delete_Blank_Rows_2 macro includes an On Error statement to trap an error that is expected to occur if there are no blank cells in the range the macro works with.

As you can see, the effect is the same and the process is very similar; but its not exactly the same. To make this point clearer, let's look at this last process (Delete_Blank_Rows_2) graphically:

You'll be happy to know that, coming back to the example, and the challenges you're facing at the office when trying to prepare a summary of the performance during 2014 of the stocks of Microsoft, Google, Apple and Amazon, this new macro achieves the same final result as Macro #1. Take a look at the image below to confirm that the rows where Microsoft share's closing price is missing are deleted.

So your boss will probably be happy with both the Delete_Blank_Rows or the Delete_Blank_Rows_2 macro 😀 . However, you may want to work with the Delete_Blank_Rows_2 macro in order to avoid using the Select method and Selection property. There are many reasons why .Select is avoided, including the facts that it can slow down your macros and increase the risk of run-time errors.

Macro #3: Delete Rows Based On Whether Cells Are Blank

Let's take a look at a third possibility for deleting rows when some of the cells are blank.

On Error Resume Next
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0

I name the macro “Delete_Blank_Rows_3”, assign the keyboard shortcut “Ctrl + Shift + B” and add a macro description. The full VBA code displayed by the VBE is the following:

You may notice 2 things when looking at this macro:

• #1: Unlike the previous macros, the VBA code doesn't specify the cell range it works with. It starts by making reference to “Selection” which, as you already know, is the current selection. Therefore, before executing the Delete_Blank_Rows_3 macro, you need to select the range of cells you want to work with. Further below I explain how you can make this selection in order to achieve your objectives. You'll be surprised by the fact that you may actually select a column that doesn't have blank cells.
• #2: The code is substantially similar to that of the Delete_Blank_Rows_2 macro. In particular, you may have noticed that the statement “Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete” is almost the sames as the statement “Range(“E6:E257″).SpecialCells(xlCellTypeBlanks).EntireRow.Delete” which appears in Macro #2 as a With…End With statement.

Let's take a look at each of the lines of code to understand what is the process followed by this macro, and how it differs from the ones that we've examined before.

Line #1: On Error Resume Next

You already know that this is an On Error statement which specifies that, when a run-time error occurs, Excel should continue executing the statement that follows that where the error occurred.

In this particular macro, the main reason for using On Error Resume Next is that the line “Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete” generates a run-time error when the selected range of cells doesn't contain any empty cells. In order to avoid the execution of the macro being stopped due to this expected run-time error, On Error Resume Next is used to allow the execution of the application to continue.

Line #2: Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

The Delete_Blank_Rows macro included a very similar statement to this one: “Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete”. However, you'll notice a few differences at the beginning of the row. Therefore, let's take a look at each of the elements separately to understand what is going on.

Element #1: Selection.EntireRow.

This is an instance of the Range.EntireRow property, which returns a range representing an entire row or rows.

The Delete_Blank_Rows_3 macro doesn't explicitly state the range of cells you're working with. For example, both the Delete_Blank_Rows macro and the Delete_Blank_Rows_2 macro worked with a particularly defined range which, in the examples, was from cell E6 to cell E257. Delete_Blank_Rows_3 works with Selection, meaning that it works with the current selection.

As a consequence of the above, what Selection.EntireRow does is:

• Takes the current selection.
• Returns an object that represents the entire row (or rows) that contain your current selection.

The way the Delete_Blank_Rows_3 macro is structured means that you don't have to actually select the column that contains the empty cells for that range to be considered. Take the Excel workbook that contains the stock performance data for Microsoft, Google, Amazon and Apple as an example. You know that the column that contains some blank cells is column E (which displays the closing price of Microsoft's share). You can, however, select column G before executing the Delete_Blank_Rows_3 macro.

And the macro still considers all the cells in each of the selected rows. This means that column E (and its blank cells) are taken into consideration by Macro #3.

Element #2: SpecialCells(xlBlanks).

You already know that this method is known as the Range.SpecialCells method and its purpose is to get an object that represents the cells matching the type and value that appear inside the parenthesis.

In the case of the Delete_Blank_Rows_3 macro, the object being fetched is a range representing the cells that are of the type xlBlanks.

You may be wondering:

What is xlBlanks? What does it stand for? Is it different from the xlCellType xlCellTypeBlanks?

The documentation surrounding the difference between xlBlanks and xlCellTypeBlanks is sometimes not as clear as you'd like. In general terms, xlBlanks was the term used in Excel 1995 and previous versions. xlCellTypeBlanks has been used since Excel 1997. However, for basic purposes of Visual Basic for Applications, both xlBlanks and xlCellTypeBlanks evaluate to the same value.

Therefore, in the Delete_Blank_Rows_3 macro shown above, “xlBlanks” refers to empty cells.

Where are the empty cells searched for?

You know that the first part of the statement (Selection.EntireRow) took the selected cells and returned an object that represented the entire rows where the selected cells were. The Range.SpecialCells method finds any empty cells in those rows.

Going back to the historical stock performance data for Microsoft, Google, Amazon and Apple, this means that even if you've selected a column that doesn't have any empty rows (such as column G in the screenshot below), the Range.SpecialCells method is able to find the blank cells in other columns of the table, such the column where Microsoft's share closing price appears (column E).

Element #3: EntireRow.Delete.

You've seen the Range.EntireRow property and the Delete method in action before in this tutorial. Therefore, I'm sure you won't have any problems figuring out what this last part of the statement does.

Any guesses?

As you probably, and correctly, imagined, the combination of this property and method takes all the rows that contain the empty cells found by the Range.SpecialCells method above and deletes them.

The Delete_Blank_Rows_3 macro has, therefore, the potential to have a very similar effect to that of the previous two macros (Delete_Blank_Rows and Delete_Blank_Rows_2). For example, if you select column G in the Excel workbook that records the historical stock performance for the year 2014 of several stocks (Microsoft, Google, Amazon and Apple) before applying the Delete_Blank_Rows_3 macro, the rows that contain blank cells (even if those blank cells are not in column G) is deleted. The screenshot below shows 2 of the rows that are deleted in such case.

Line #3: On Error GoTo 0

The purpose of the On Error GoTo 0 statement is to disable any error handler. In the case of the Delete_Blank_Rows_3 macro, Error GoTo 0 disables the On Error Resume Next error handler that appears in the first line of the macro's body.

Summary Of Process Followed By Delete_Blank_Rows_3 Macro

The following summary explains, in simple terms, the gist of the process followed by the Delete_Blank_Rows_3 macro:

• Step #1: The macro takes your current selection and gets the entire rows that contain that selection.
• Step #2: The macro searches for empty cells in those rows that have been obtained.
• Step #3: If empty cells are found, the entire row where they are found is obtained.
• Step #4: The rows where the empty cells were found are deleted.

The relevant flow chart looks roughly as follows. You may notice that this diagram is very similar to the process followed by the Delete_Blank_Rows macro. However, if you compare the snippets of VBA code, you'll notice that there are some important differences between the 2 procedures. In particular, the Delete_Blank_Rows_3 macro doesn't use the Select method (even though it uses Selection). As I explain at the end of the previous section, it is common advice to avoid using .Select when possible.

The image below shows the Delete_Blank_Rows_3 macro in action. For these purposes, and in order to continue with the same example used above, I have selected column G before executing the macro. In the sample Excel workbook that shows the results of applying Macro #3, I have added back 3 rows in order to include the header that I usually put in sample Excel workbooks.

This Excel VBA Delete Blank or Empty Rows Tutorial is accompanied by Excel workbooks containing the data and macros I use. You can get immediate free access to these example workbooks by subscribing to the Power Spreadsheets Newsletter.

Macro #4: Delete Rows When The Entire Row Is Empty (Option 1)

Macros #1 through #3 are very helpful if you want to delete entire rows when a particular cell in that row is blank. But this may not work for you in all cases.

Let's take a look again at the stock performance data during 2014 of Microsoft, Google, Apple and Amazon.

But first…

Do you remember your coworker? The one who doesn't know how to use Excel and accidentally deleted some of the closing prices for Microsoft's share?

Well… It looks that he has been “working” again on this particular Excel workbook and, accidentally, added several blank rows throughout the table. Your boss opens this file and doesn't like what he sees:

Since he knows you're the office's Excel wizard, he asks you to please delete those rows.

How can you do it?

With a macro, of course! You don't want to waste your time going manually through the whole table searching for rows that are completely empty.

So let's do it by using the following VBA code:

Dim iCounter As Long
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    For iCounter = Selection.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(Selection.Rows(iCounter)) = 0 Then
            Selection.Rows(iCounter).EntireRow.Delete
        End If
    Next iCounter
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

I name the macro “Delete_Empty_Rows”, assign the keyboard shortcut “Ctrl + Shift + M” and include a brief description. The whole macro looks as follows in the VBE.

Similar to the Delete_Blank_Rows_3 macro, Delete_Empty_Rows works within a selected range. This means that it deletes the blank rows of the range that you have selected and, therefore, you must have selected the range of data you want to work with (delete the empty rows from) before executing the macro.

In order to understand this particular macro better, let's take a look at what each of the lines of code does.

Line #1: Dim iCounter As Long

The Dim statement is used to declare a variable. In this case, the declared variable is iCounter. The As keyword is used to declare iCounter as a variable of the Long data type. Therefore, iCounter holds integer numbers.

Theoretically, iCounter can also be declared as a variable of the Integer data type. This macro uses Long instead of Integer because the Long data type can contain integers that are larger than those that fit in the Integer data type. In other words, Long is helpful in case the range you select to apply the macro to has too many rows.

You may wonder:

What is the purpose of declaring this variable?

The iCounter variable, as implied by its name, serves as counter. The reason why the Delete_Empty_Rows macro needs a counter is that it is structured as a loop. This loop is implemented using a For… Next statement (which I explain below) that repeats a particular group of statements a certain number of times and, therefore, needs a control variable (in this case iCounter) to keep track of how many times the loop has been repeated.

If you don't fully understand this explanation, don't worry… Just keep reading; by the end of this section you will understand perfectly all of the above.

Line #2: With Application

You already know that the word “With” is a signal that a With…End With statement is beginning. You also know that the usual syntax for the first line of such a statement is “With objectExpression”, where “objectExpression” is an expression that evaluates to an object.

The Application object is Excel itself (the Microsoft Excel application).

As a consequence of the above, you know that the series of statements that appear within the With…End With statement all work with and make reference to the Excel application itself.

The following screenshot shows the whole With…End With statement.

Line #3: .Calculation = xlCalculationManual

This line is within the With…End With statement that makes reference to the Excel application. Therefore, you can think of it as being “Application.Calculation = xlCalculationManual = xlCalculationManual”.

The Application.Calculation property is used to set the calculation mode that Excel applies to the workbook. In this particular statement, the chosen mode is xlCalculationManual, which means that Excel doesn't re-calculate the workbook automatically, but rather when you request it.

In other words, this statement turns off the automatic workbook calculation. The purpose of doing this is to make the macro faster.

Line #4: .ScreenUpdating = False

Just as with the previous statement, this line works with the Excel application and, therefore you can think of it as being “Application.ScreenUpdating = False”.

The Application.ScreenUpdating property is used to turn off screen updating. The purpose of this is the same as that of turning off the automatic workbook calculation: to speed up the macro code.

Line #5: For iCounter = Selection.Rows.Count To 1 Step –1

This line of code is the beginning of a For… Next statement, which is a common way of structuring a loop in Visual Basic for Applications. This statement asks Excel to execute a group of statements repeatedly a certain number of times.

The usual syntax for the first line of a For… Next statement is “For counter [As datatype] = start To end [Step step]”. This line, basically, tells Excel how many times to perform the loop by creating a counter that counts the repetitions, stating what are the starting and ending values for that counter and (optionally) by what amount should the counter increment or decrement every time the loop is repeated.

In the Delete_Empty_Rows macro, the counter is the variable iCounter. Its starting value is the number of rows in the range you've selected to work with before executing the macro, its ending value is 1 and it decreases by 1 every time the loop is executed. In other words, the loop is repeated a number of times equal to the number of rows in the range you're working with. This makes sense since you want Excel to check each row of that range to confirm if its blank and, therefore, should be deleted.

But let's take a closer look at each of the elements of this line of VBA code within the Delete_Empty_Rows macro to understand how its structured:

• “iCounter” is the Counter argument, which is a required item of the For… Next statement. This argument is a control variable, meaning that it keeps track of the number of times the body of the loop is repeated.
• The datatype is not specified within the body of this loop, as iCounter has already been defined before (in the first statement of Macro #4) as a Long data type variable.
• “Selection.Rows.Count” (which takes the place of “start” in the statement syntax) determines the initial value of the counter. This initial value is equal to the number of rows in the range that you've selected to work with before executing the macro. Let's look precisely at how this statement tells Excel what the starting value for the counter should be.
  • #1: Selection.Rows is an example of the Range.Rows property, which returns an object representing the rows in a particular range. You already know that Selection returns the current selection. In other words, Selection.Rows returns the rows in the table (or range of data) you've selected to apply the macro to.
  • #2: The Range.Count property, as you may imagine, counts the number of rows in the range you're working with and returns that value.
• “To 1” tells Excel what is the final value of the counter which, clearly, is 1. As you can see, the number 1 takes the place of “end” in the general syntax of the For… Next statement.
• “Step –1” dictates the value by which the counter changes each time Excel goes through the loop. In this case, the control variable iCounter decreases by 1 each time Excel repeats the body of the loop. In the case of the Delete_Empty_Rows macro, the value –1 takes the place of the optional item “step” in the general syntax of the For… Next statement.

You can see the whole For… Next statement of the Delete_Empty_Rows macro in the image below:

Line #6: If WorksheetFunction.CountA(Selection.Rows(iCounter)) = 0 Then

This is the first row of an If…Then…Else statement. These statements are one of the most common ways to implement a condition in Visual Basic for Applications.

The syntax of the first line of an If…Then…Else statement is “If condition [Then]”. In the line of VBA code that appears above, the condition that is tested is whether WorksheetFunction.CountA(Selection.Rows(iCounter)) = 0. If this condition evaluates to true, Excel executes the statement that appears immediately below (which I explain in the next step).

However, the question is:

What does the condition WorksheetFunction.CountA(Selection.Rows(iCounter)) = 0 mean?

The question asked by this logical test is whether all the cells in a particular row are empty. Let's take a look at each of its elements separately to understand how the question is structured in VBA terms:

• The WorksheetFunction object is a container for regular worksheet functions. In other words, you use the WorksheetFunction object to call worksheet functions.
• The WorksheetFunction.CountA method is the rough equivalent of the COUNTA Excel function. In other words, this method counts the number of cells in a particular range that aren't empty. The syntax of this particular method is “expression.CountA(arguments)”, where “expression” is a particular variable that represents a WorksheetFunction and “arguments” are the arguments representing the values to be counted. In the Delete_Empty_Rows macro, the arguments to be counted are Selection.Rows(iCounter).
• In Macro #4, Selection.Rows(iCounter) represents a row in the range of data you're applying the macro to. The precise row represented by this argument depends on the current value of the iCounter variable. As you already know, the value of iCounter changes in accordance with the conditions included at the beginning of the For… Next statement. For example, the first time that Excel runs the body of the loop, the row used to determine whether the condition evaluates to true or false is the last row in the range that the macro is working with (Row(Selection.Rows.Count)). The last time that Excel repeats the loop, Selection.Rows(iCounter) refers to the first row in the range (Row(1)).
• The last part of the statement (“= 0”) sets the condition that the expression to the left of the equal sign (WorksheetFunction.CountA(Selection.Rows(iCounter))) be equal to 0. The WorksheetFunction.CountA method is only equal to 0 if all of the cells in the row that is being checked (Selection.Rows(iCounter)) are empty. If there is at least one cell that is not empty, the method returns a different value and the condition evaluates to false.

Line #7: Selection.Rows(iCounter).EntireRow.Delete

This is the only piece of VBA code within the If…Then…Else statement and, therefore, is the only statement that is executed if the condition above evaluates to true (all the cells in a particular row are empty).

Do you know what this statement does?

You already know all of the elements in this row and, therefore, probably answered that this piece of VBA code deletes the entire row. In any case, let's go quickly through each of the items:

• Selection.Rows(iCounter) represents a row in the range that the Delete_Empty_Rows is working with.
• .EntireRow selects the entire row.
• .Delete deletes the selected object which, in this case, is the selected row.

Line #8: End If

This line simple terminates the If…Then…Else statement.

To summarize, the If…Then…Else block:

• Asks whether a row in the selected range is empty.
• If the row is empty, it selects the entire row and deletes it.

Line #9: Next iCounter

This is the last line of the For… Next statement.

Once Excel has performed the activities that are specified within the loop, it should move to the next element which, in this case, is the next row of the selected range.

Line #10: .Calculation = xlCalculationAutomatic

You already know that the statement .Calculation = xlCalculationManual that appears early in the With…End With statement of this macro turns off the automatic workbook calculation.

Can you imagine what does the statement .Calculation = xlCalculationAutomatic does?

Yes. As you've probably answered, it simply turns back on the workbook calculation or, more precisely, allows Excel to control the recalculation.

Line #11: .ScreenUpdating = True

I'm sure that you know what this statement does, but I'll ask the question anyway:

Do you know what .ScreenUpdating = True does?

Since you know that .ScreenUpdating = False turns off screen updating, you probably imagine that .ScreenUpdating = True turns screen updating back on. Doing this (turning screen updating back on) is quite important, so set the ScreenUpdating property back to True when the macro you're creating ends.

Line #12: End With

This row terminates the With…End With statement.

Summary Of Process Followed By Delete_Empty_Rows Macro

In a nutshell, the Delete_Empty_Rows macro proceeds as follows:

• Step #1: The macro goes to the last row of the range you've selected.
• Step #2: The macro checks whether that particular row is completely empty.
• Step #3: If the row is completely empty, the macro deletes the row. If the row is not completely empty, it doesn't do anything.
• Step #4: The macro moves to the row immediately above the one it has just analyzed and repeats the same process, starting with the step #2 above.
• Step #5: The macro continues moving up through the selected range until it reaches the first row (at which point it ends).

In addition to the above, Delete_Empty_Rows turns of automatic workbook calculations and screen updating for purposes of speeding up the macro. Once the process of deleting empty rows is completed, automatic workbook calculations and screen updating are turned back on.

Graphically, this process looks roughly as follows:

The image below shows how the Delete_Empty_Rows macro works in practice:

Macro #5: Delete Rows When The Entire Row Is Empty (Option 2)

An alternative to the Delete_Empty_Rows macro which also deletes an entire row when it is completely empty is as follows:

Dim aRow As Range
Dim BlankRows As Range
For Each aRow In Selection.Rows.EntireRow
    If WorksheetFunction.CountA(aRow) = 0 Then
        If Not BlankRows Is Nothing Then
            Set BlankRows = Union(BlankRows, aRow)
        Else
            Set BlankRows = aRow
        End If
    End If
Next
If Not BlankRows Is Nothing Then
    Application.ScreenUpdating = False
    BlankRows.Delete
    Application.ScreenUpdating = True
End If

I name this macro Delete_Empty_Rows_2, assign the keyboard shortcut “Ctrl + Shift + P” and add a description. The whole macro code looks as follows in the Visual Basic Editor:

As usual, let's take a look at each of the lines of code to understand how the macro proceeds. Now, I have to warn you that the structure of this macro is slightly trickier than that of the other macros we've explored in this tutorial so, if you decide to continue ahead, please bear with me. By the end, you'll be able to understand the process followed by this macro

Line #1 And Line #2: Dim aRow As Range | Dim BlankRows As Range

You already know that:

• The Dim statement is used for purposes of declaring a variable.
• The As keyword is used to identify the data type of a variable in the declaration statement.

In the Delete_Empty_Rows_2 macro the variables declared are “aRow” and “BlankRows”. Both are declared as Range objects.

Within Macro #5, these variables are used for purposes of representing rows. More specifically, BlankRows is used to store and represent all of the blank rows within the cell range you've selected.

Line #3: For Each aRow In Selection.Rows.EntireRow

This row signals the beginning of a For Each… Next statement, whose purpose is to repeatedly execute a group of statements for each component of an object collection or array.

This image shows the whole For Each… Next statement in the Delete_Empty_Rows_2 macro:

The basic syntax of the first line of a For Each… Next statement is “For each element [as datatype] in group”, where:

• “element” is a required variable that is used for purposes of iterating through the different elements of the collection or array. I cover the topic of arrays in more detail in this blog post. In the Delete_Empty_Rows_2 macro, “element” is the aRow variable that was declared at the beginning of the macro as a range. In this case, since the element was already declared, there is no need to state its data type in the For Each… Next statement. When the element has not been previously declared, its necessary to include the data type of the element in the For Each… Next statement.
• “group” is a required object that refers to the collection or array over which the statements within the For Each… Next statement are repeated. For Delete_Empty_Rows_2, the group is Selection.Rows.EntireRow. Perhaps you remember that Selection.Rows is a possible implementation of the Range.Rows property. You may also remember that, when the range is Selection, this item returns the rows in the range of data that is currently selected. Finally, the EntireRow property makes reference to one or more rows.

Therefore, this line says that statements within the For Each… Next statement should be executed and repeated for each row in the range of data (or table) that you have selected before executing the Delete_Empty_Rows_2 macro.

Notice that, in practice, the effect of this line is very similar to that the opening statement of the For… Next statement used in the Delete_Empty_Rows macro (For iCounter = Selection.Rows.Count To 1 Step –1). The main reason their syntactical structures are different is because one is a For… Next statement (which repeats a group of statements a certain number of times) and the other is a For Each… Next statement (which repeats a group of statements for each element of an object collection).

Line #4: If WorksheetFunction.CountA(aRow) = 0 Then

You may recognize that this is the first row of an If…Then…Else statement and, therefore, know that the purpose of this statement is to set a condition in VBA.

The following image shows the whole If…Then…Else statement:

Usually, these type of statements use the syntax “If condition [Then]”. Therefore, you can identify that the condition being tested above is whether “WorksheetFunction.CountA(aRow) = 0”.

What does this condition mean? What is it asking?

You may remember that the Delete_Empty_Rows macro also included an If…Then…Else statement. In that opportunity, the question was similar. More precisely, the condition tested was whether “WorksheetFunction.CountA(Selection.Rows(iCounter)) = 0”. This was asking the question of whether all the cells in a particular row were empty.

Based on this, can you imagine what condition is being tested by “WorksheetFunction.CountA(aRow) = 0”?

You already know that the Worksheet Function object is a container for worksheet functions and that the WorksheetFunction.CountA method counts the number of cells in a particular range that aren't empty. Therefore, you probably already noticed that the question being asked by the Delete_Empty_Rows_2 macro is whether all the cells in a particular row are empty.

Line #5: If Not BlankRows Is Nothing Then

You may recognize that this is a second If…Then…Else statement. The image below shows it:

The structure of the condition in this particular statement, however, differs from that of the other If…Then…Else statements we've seen throughout this tutorial. In this case, the question being asked is whether “Not BlankRows Is Nothing”.

Let's take a look at each of the items individually to understand the meaning of this line:

• The Not operator returns the opposite logical value of the applicable expression. In other words, Not does the following (as applicable):
  • Converts True to False; or
  • Converts False to True.
• BlankRows is one of the variables that was declared at the beginning of the macro.
• The Is operator compares 2 object reference variables. In other words, “Is” determines whether 2 object references refer to the same object without making a value comparison. What matters is whether they both refer to the same object.
• Nothing is the default value for a particular data type. For example, in the case of the Object data type, the default value is Nothing (a null reference).

So what is this statement doing?

The Is operator is used to determine whether the object reference “Not BlankRows” makes reference to the same object as Nothing. “Not BlankRows”, as you may be able to conclude by now, is simply the opposite of the BlankRows variable.

Therefore, the question asked by this logical test is whether the opposite of BlankRows makes reference to the same object as Nothing. In other words, it asks: does BlankRows make reference to something?

The question you may have is:

When is the opposite of the variable BlankRows (Not BlankRows) making reference to the same object as Nothing?

The question is trickier than it looks like because the row we're looking at right now is within a loop (implemented with a For Each… Next statement), as shown below:

To complicate things even further, the If…Then…Else statement that we're currently looking at is used to assign values to the BlankRows variable. I explain how this occurs further below.

As a consequence of the above, the value of BlankRows changes every single time the macro finds a new blank row. Let's take a more detailed look at how this change happens:

• The first time(s) the loop runs, BlankRows isn't assigned to anything. Therefore, it's nothing and makes reference to the same object as Nothing. This means that “Not BlankRows” doesn't makes reference to the same object as Nothing and the condition “Not BlankRows Is Nothing” evaluates to false. The consequence of this is that, if there are any empty rows in the range you've selected before executing the macro, the value aRow is stored in the variable BlankRows once the macro finds the first empty row. I explain how this is done further below.
• Once the first empty row is found, BlankRows stores the value of aRow. Therefore, now it is something and makes reference to the opposite of Nothing. This means that “Not BlankRows” makes reference to the same object as Nothing and the condition “Not BlankRows Is Nothing” evaluates to true. The consequence of this is that the value Union(BlankRows, aRow), whose meaning I explain below, is stored in BlankRows.
• All the following times the loop runs, BlankRows stores the value of the previous Union(BlankRows, aRow). Therefore, the condition continues to evaluate to true and the value stored in BlankRows is updated accordingly.

If this doesn't make much sense to you, please hold on until the end of the loop. By then you'll understand much more clearly what this set of statements does.

Line #6: Set BlankRows = Union(BlankRows, aRow)

This row is within the If…Then…Else statement. This is the statement that Excel executes if the logical test that appears in the previous row (If Not BlankRows Is Nothing) evaluates to true.

Let's take a look at each of the individual items:

• The Set statement assigns an object reference.
• BlankRows is one of the variables declared at the beginning of Macro #5.
• Union returns the union of several ranges. In other words: The union of 2 sets is the set of elements belonging to at least one of these 2 sets. The statement we're looking at makes reference to the union between BlankRows and aRow, meaning that the union includes all elements that belong to (i) both BlankRows and aRow, (ii) only BlankRows and (iii) only aRow. Graphically:
  
  

Therefore, this statement takes the variable BlankRows and assigns to it the union of the variables BlankRows and aRow.

The question you may have is, what does this mean in practical terms for the Delete_Empty_Rows_2 macro?

To answer this question, you need to remember 2 things:

• The If…Then…Else statement we're looking at is within a For Each… Next statement (For Each aRow In Selection.Rows.EntireRow) that goes through each individual row of the table or range you're working with. For each of these rows, the macro asks whether the whole row is empty (If WorksheetFunction.CountA(aRow) = 0).
• The first time the loop runs, BlankRows is nothing and the logical test “If Not BlankRows Is Nothing” evaluates to false. This continues to be the case until the first blank row is found and assigned to the BlankRows variable. Thereafter, the condition evaluates to true and the assignment made by this row (Set BlankRows = Union(BlankRows, aRow)) only occurs then. In addition to the above, when the condition evaluates to false (which occurs only until the loop finds an empty row), the statement that is executed (Set BlankRows = aRow) sets BlankRows to aRow. In other words, immediately after the first time the group of instructions within the loop finds a blank row, BlankRows is equal to aRow. This looks roughly as follows in Excel:

By the next time the instructions within the loop are executed, BlankRows is already equal to the first empty row (which was aRow). Additionally, the macro selects the second empty row (which is now aRow). Therefore, BlankRows is set to the union of BlankRows and aRow. In Excel, this looks as follows:

The following time the loop runs, BlankRows is equal to the union of BlankRows and aRow which (at that point) is 2 empty rows. In addition to this, the macro selects the third empty row (aRow). BlankRows is then set to this new union of BlankRows and aRow.

By now you probably get the idea. Any empty rows that Excel finds are “added” to the variable BlankRows. Once the loop has run as many times as required, BlankRows represents all the empty rows in the table or range.

Line #7: Else

This row is part of the If…Then…Else statement. It signals that the rows that follow it (the Else statements) are only executed if none of the previous conditions has evaluated to true.

In the Delete_Blank_Rows_2 macro, the else statements are executed if the condition “If Not BlankRows Is Nothing” evaluates to false. This occurs until the instructions within the loop signaled by the statement “For Each aRow In Selection.Rows.EntireRow” find the first blank row.

In other words, “Else” signals that the statements that follow it are those to be executed the first time(s) the loop in Macro #5 runs. More precisely, these statements are executed until the macro finds the first empty row.

Line #8: Set BlankRows = aRow

You probably already know what this statement does:

It sets the variable BlankRows to aRow.

Please refer to the explanation of the statement “Set BlankRows = Union(BlankRows, aRow)” above for a thorough explanation of how Macro #5 manages the variable BlankRows.

Line #9 And Line #10: End If | End If

As you already know, these statements simply terminate the If…Then…Else statements. The reason there are 2 End If statements is, simply, that 2 separate If…Then…Else statements are being terminated.

Line #11: Next

You already know that this row terminates the For Each… Next loop.

Line #12: If Not BlankRows Is Nothing Then

We saw this exact same statement further above. You know that it signals the beginning of an If…Then…Else statement.

Furthermore, you know that the question asked by the logical test is whether BlankRows makes reference to something.

Line #13: Application.ScreenUpdating = False

We've already seen a substantially similar statement to this one in the Delete_Empty_Rows macro.

Therefore, you know that the Application.ScreenUpdating property turns of screen updating for purposes of speeding up the macro.

Line #14: BlankRows.Delete

The macros included in this tutorial have applied the Delete method in several instances. For example, the Delete_Blank_Rows_3 macro combined the Delete method with an instance of the Range.EntireRow property to delete whole rows based on whether a cell in the row was empty or not. In that case, the whole statement was “Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete”.

Considering the above, can you guess what this statement does?

You probably answered (correctly) that this statement takes all the empty rows represented by the variable BlankRows and deletes them.

Since this particular row is within an If…Then…Else statement, it is only applied if the variable BlankRows makes reference to something. This is always the case unless you're working with a table or range that has no empty rows at all.

Line #15: Application.ScreenUpdating = True

You already know that its important to turn the ScreenUpdating property back on when a macro ends, and that is precisely what this line does.

Line #16: End If

This statement terminates the third, and last, If…Then…Else statement of the Delete_Empty_Rows_2 macro.

Summary Of Process Followed By Delete_Empty_Rows_2 Macro

As usual, let's wrap up by summarizing the process followed by the Delete_Empty_Rows_2 macro.

• Step #1: The macro goes through each row in the range that you've selected and asks whether it is completely empty.
• Step #2: Once the macro has found the first empty row in the range, it assigns it to the variable BlankRows.
• Step #3: Once the macro founds the second empty row in the selected range, it adds it to the variable BlankRows. Therefore, BlankRows now represents the first 2 empty rows in the relevant cell range.
• Step #4: The macro repeats steps #1 and #3 until it has found all the empty rows in the range you're working with. Each row is added to the variable BlankRows so, by the end, BlankRows represents all empty rows.
• Step #5: All empty rows, represented by the variable BlankRows, are deleted.

In addition to the above, and for purposes of speeding up the macro, Delete_Empty_Rows_2 turns off screen updating prior to step #5. Screen updating is turned back on after the empty rows have been deleted.

Graphically, this whole process looks as follows:

The following image shows you the Delete_Empty_Rows_2 macro at work:

Conclusion And Other Possible Modifications To The Macros In This Tutorial

The 5 macros that appear in this tutorial are, by no means, an exhaustive list of all the possible ways in which you can code a macro to delete blank rows or rows with empty cells in Excel.

As you become more proficient in Visual Basic for Applications and improve your knowledge about macros, you'll find ways to tweak and improve the macros that I have explained above. Perhaps more importantly, you'll begin to understand why a particular structure may be more appropriate than another one considering the context you're working in and the objectives you want to achieve.

The following is a very brief list of possible ways to modify the macros that appear in this tutorial. The purpose is only to give you an idea of the questions you may start to consider in the future when creating a macro to delete blank rows or rows with empty cells in Excel.

• The Delete_Blank_Rows_2 macro uses a With…End With statement. Is such a structure necessary? How about using a single line of code?
• Would any of the macros that appear in this tutorial benefit from including On Error statements for purposes of handling run-time errors?
• Would it be useful to create additional dialog boxes for any macros? How about a dialog box confirming that the blank rows have been removed? How about adding dialog boxes where the users of your Excel workbook can input, for example, the cell range they want a particular macro to search for blank cells in?
• I explained why some Excel experts suggest that you avoid using the Select method and Selection property. Some of the macros that appear in this tutorial use other structures to achieve the goal of deleting blank rows or rows with empty cells without using Select or Selection. Can you think of other alternatives you can use to avoid using the Select method or the Selection property? How about using variables and assigning ranges in the VBA code?
• Is there something you can do to handle cells that have blanks created by double quotes (“”)? How about using “.Value = .Value” as implemented in the Delete_Blank_Rows_2 macro?
• The Delete_Empty_Rows_2 macro structures a conditional test as “If Not BlankRows Is Nothing”. Could you ask a substantially similar question using a different structure? How about using “If BlankRows Is Nothing”?