Harnessing the Power of an Excel Macro to Delete Rows in a Spreadsheet

In our fast-paced, technology-driven world, it doesn’t make sense to spend time clicking through rows and rows on a spreadsheet just to delete them. By using the power of Excel Macros, you can automate the process so that Excel will eliminate any and all flagged rows instantaneously.

Let’s take a real-world example. Transfiguration, Inc., is a distributor of medical supplies all over the world. This multi-million dollar company divides sales by countries and regions, and the head staff accountant for Southeast Asia wants to delete all inactive accounts from the company’s spreadsheet so he can see current customers’ information more clearly. In the sample of customer information below, every inactive account is marked with FALSE. The code explained in this post will loop through all the rows of the spreadsheet, deleting all inactive accounts in Excel.

Set Up the Data

The example below includes account numbers of Transfiguration customers in the Southeast region, the last time each customer made a purchase from Transfiguration, and whether the customer’s account is active (TRUE) or inactive (FALSE). Remember, the accountant wants to delete all inactive accounts in Excel, so we will create a macro to delete all rows in which “Account Active” is FALSE.

Before You Start

In this case, the accountant might find it easier to begin by recording a macro rather than simply opening the Visual Basic window and typing in Excel. To do this, on the Developer tab click Record Macro and immediately Stop Recording. This macro performs no actions, but it sets up the framework to modify.

It’s important to remember to turn on developer mode in Excel. The rightmost tab on the ribbon should say Developer. If not, then turn it on by right-clicking the ribbon, choosing Customize Ribbon, and checking the box for Developer.

The Variables

(The following describes the details of the Macro functions. The completed Macro is below and can be copied and pasted directly into your spreadsheet.)

The Excel macro must know where to find the accounts to delete. This code is set up to use the region surrounding the selection—the same region you can select by pressing -*.

Set rngRange = Selection.CurrentRegion

The macro also needs to know which column contains the test value. The code is set to use the column of the selection when you execute the macro, which it pulls from ActiveCell.Column. If you have pasted the data in columns A:C, then be sure the selection point is in column C, anywhere within the data table, when you start the macro.

lngCompareColumn = ActiveCell.Column

Finally, the macro must know how many rows to loop through. It uses three variables, one for the number of rows, one for the first row, and one for the last row.

lngNumRows = rngRange.Rows.Count
lngFirstRow = rngRange.Row
lngLastRow = lngFirstRow + lngNumRows - 1

The Loop

We want to execute a single line of code once for each row of customer information in the Excel table. To do that, we’ll use a for loop, beginning at the last row and moving up to the first row. Handling the rows in reverse order eliminates the potential problem of deleting a row, then advancing to the next row, and finding that the rows have shifted after the deletion.

For lngCurrentRow = lngLastRow To lngFirstRow Step -1
 If (Cells(lngCurrentRow, lngCompareColumn).Text = "FALSE") Then _
 Rows(lngCurrentRow).Delete
 Next lngCurrentRow

For each row, the macro checks whether the text in the comparison column is FALSE. If so, then it deletes the account. Then it moves on to the next row (in reverse order) until it has executed the loop once for each row in the table.

Complete Macro

(Copy and paste the information into recorded entry Macros you created.)

Here’s the full Excel code, from start to finish. Note that the final code include the dim statements, comments, and statements to turn off the screen updates and turn them back on at the end.

Sub DeleteRows()
 '
 ' Macro to delete rows where selected column is "FALSE"
 '
 Dim rngRange As Range
 Dim lngNumRows, lngFirstRow, lngLastRow, lngCurrentRow As Long
 Dim lngCompareColumn As Long

'Set the range to the current region around the selection point
 'Find the number of rows and the starting row based on the size of the region
 'Use the column of the current selection as the comparison column

Set rngRange = Selection.CurrentRegion
 lngNumRows = rngRange.Rows.Count
 lngFirstRow = rngRange.Row
 lngLastRow = lngFirstRow + lngNumRows - 1
 lngCompareColumn = ActiveCell.Column

'For faster execution, turn off the screen refresh
 Application.ScreenUpdating = False

'For each row, check to see if the comparison column is false. If so, delete it.
 For lngCurrentRow = lngLastRow To lngFirstRow Step -1
 If (Cells(lngCurrentRow, lngCompareColumn).Text = "FALSE") Then _
 Rows(lngCurrentRow).Delete
 Next lngCurrentRow

'Turn the screen updates back on
 Application.ScreenUpdating = True

End Sub

To run the macro, highlight the column of true/false content and run the macro by selecting the short cut you entered when you created it. (Be sure you are running the correct shortcut. We found that if we used a shortcut already taken, it automatically created a new shortcut for us. To see the shortcut, open your Macros and check the top of the page.)

After completing this Excel macro, the Transfiguration accountant now has a table only of current customer accounts. It is easier to read, and isn’t littered with irrelevant information. In this way, you can use Excel macros to effectively organize information to make your life a little more comfortable.