June 23rd, 2007

How Can I Delete Specified Rows in an Excel Spreadsheet?

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I delete a row in an Excel spreadsheet if there is a specific value in column A of that row?

— OI

SpacerHey, Scripting Guy! AnswerScript Center

Hey, OI. Before we get rolling on today’s column, have we mentioned that time is beginning to run out on the Scripting Guys TechEd Challenge? We have? Oh. Well, we’re going to mention it again anyway, because time really is running out on the TechEd Challenge: you only have until 11:59 PM on June 30th to submit your entry. And why would you even want to submit your entry? That’s easy; after all, the top five scorers are going to win a Scripting Guys gift pack consisting of:

A Dr. Scripto bobblehead doll

A Scripting Guys coffee mug

A Scripting Guys T-shirt

A copy of Dr. Scripto’s Fun Book

Anything else we have lying around the office that we’re hoping to get rid of

Now, admittedly, the competition is fierce: we’ve actually had people submit perfect scores. (Much to our surprise, and somewhat to our chagrin: when we first issued the challenge we said that we would rename the Script Center, for one day, after anyone who received a perfect score. If you happen to run into anyone from TechNet, don’t tell them that we’re going to be renaming the Script Center soon. We’d kind of like to … surprise … them with that. No doubt they’ll be thrilled.)

However, in keeping with Script Center tradition, you don’t have to be a winner to be a winner. All the entries we receive (even from people who somehow fail to earn a single point) will go into a hat; from there we’ll draw the names of 10 lucky souls who will receive their very own Dr. Scripto bobblehead doll. Does it get any better than that? In a word: no.

Of course, that does lead to an obvious question: how do you even begin to prepare yourself to undergo something as rigorous and demanding as the TechEd Challenge? That’s also an easy one. How do you prepare for the TechEd Challenge? The same way you prepare for any of life’s challenges: by learning how to write a script that can delete a row in an Excel spreadsheet if there is a specific value in column A of that row.

Fortunately, we just happened to have a script that does that very thing:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)

i = 1

Do Until objExcel.Cells(i, 1).Value = “” If objExcel.Cells(i, 1).Value = “delete” Then Set objRange = objExcel.Cells(i, 1).EntireRow objRange.Delete i = i – 1 End If i = i + 1 Loop

Let’s see if we can figure out how this script works. (At the risk of spoiling the suspense, we’ll note that there is one plot twist that you’ll need to pay special attention to.) We start out by creating an instance of the Excel.Application object and setting the Visible property to True; that gives us a running instance of Excel that we can see on screen. After that we call the Open method to open the file C:\Scripts\Test.xls, then assign the value 1 to a counter variable named i (a variable we’ll use to keep track of the current row in the worksheet). At that point, we’re ready to start whacking rows in the spreadsheet.

Speaking of which, for educational purposes our spreadsheet looks something like this:

Microsoft Excel


As you can see, we have 9 rows of data; in column A of each of these rows we have either the word delete or the word keep. Our script is going to methodically look at the value of column A in each row. If it finds the word delete the script will delete that row; if it finds the word keep the script will keep that row. If everything goes according to plan (and, with the Scripting Guys, everything always goes according to plan … sort of) when the script finally comes to a halt the spreadsheet should look like this:

Microsoft Excel


All we have to do now is figure out how to get from Point A to Point B.

As highly-trained professionals, the Scripting Guys know that you’re unlikely to get from Point A to Point B unless you start out by doing something. Therefore, our first step is to set up a Do Until loop that runs until we encounter a blank cell in column A:

Do Until objExcel.Cells(i, 1).Value = “”

As you can see, all we’re doing here is checking to see if cell i, 1 (row i, column 1) is equal to an empty string (“”). If it is, then we’re done: that means we’ve looked at all the rows in the spreadsheet (or at least all the rows that contain data). If it’s not empty, well, then we have some work to do.

In particular, what we have to do is determine the actual value of the cell in question: we need to know if the value is equal to delete. If it’s not, well, no big deal: we simply increment the counter variable i by 1, then loop around and repeat the process with the next row in the spreadsheet. But suppose the value is equal to delete? What do we do then?

Here’s what we do then, we execute this block of code:

Set objRange = objExcel.Cells(i, 1).EntireRow
objRange.Delete
i = i – 1

In the first line we create a Range object that begins in cell i, 1 and then proceeds to gobble up the rest of the row as well. (How do we know that this range encompasses the entire row? Because we used the EntireRow property when we created the range.) In line 2 we then call the Delete method and delete the row in question. That’s pretty east, and pretty straightforward.

The third line in our little code block is the tricky part. Why i = i – 1? Having deleted one row in the spreadsheet our thoughts should lgoically turn to the next row in the spreadsheet. Shouldn’t this line of code be i = i + 1 instead?

Believe it or not, no; it shouldn’t be. And here’s why.

Suppose we’re on row 2 of the spreadsheet. What happens if we delete this row? That’s right: row 2 disappears, but what used to be row 3 in the spreadsheet moves up and becomes the new row 2. In other words, each time we delete a row the remaining rows in the worksheet renumber themselves. What used to be row 3 is now row 2.

Of course, there’s nothing wrong with that, but it does lead to one problem: we haven’t checked the value of this new row 2 yet. That’s why we decrement the value of our counter variable: if i was equal to 2, we subtract 1 from that value and make i equal to 1. That way, when we encounter the line of code that increments the counter variable, i will be set to 2. Doesn’t that mean we’re going to check row 2 again? Yes, and that’s the point: remember, this is a brand-new row 2, one that we haven’t checked yet.

Yes, we know: it is a little confusing, isn’t it? However, do this: run the script once with a sample spreadsheet and see what happens. When the script finishes, comment out the line i = i – 1 and re-run the script. At that point it should be clear why we need to perform this little trick.

And that, believe it or not, is all we have to do. When we’re all done the rows marked delete will be deleted, and the rows marked keep won’t be deleted. Which means that, for once, at least, things really did go according to plan.

And remember: enter the TechEd Challenge while there’s still time. Things are still a bit up in the air, but, for better or worse, this might be the last chance you ever have to get a Dr. Scripto bobblehead doll that you can call your own. As it stands right now, the Scripting Guys budget for the upcoming fiscal year is – let’s see, divide by 12, carry the 2, round up to the nearest dollar … 0 dollars and 0 cents. Needless to say, that means that this might very well be a once-in-a-lifetime opportunity.

Oh: and don’t forget that, if you get a perfect score, for one day we’ll rename the Script Center after you. That’s also likely to be a once-in-a-lifetime opportunity, especially when TechNet finds out what we’re doing ….

Author

0 comments

Discussion are closed.