{"id":64593,"date":"2007-06-23T00:12:00","date_gmt":"2007-06-23T00:12:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/06\/23\/how-can-i-delete-specified-rows-in-an-excel-spreadsheet\/"},"modified":"2007-06-23T00:12:00","modified_gmt":"2007-06-23T00:12:00","slug":"how-can-i-delete-specified-rows-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-delete-specified-rows-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I Delete Specified Rows in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

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 <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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

A Dr. Scripto bobblehead doll<\/B><\/A><\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

A Scripting Guys coffee mug<\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

A Scripting Guys T-shirt<\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

A copy of Dr. Scripto\u2019s Fun Book<\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

Anything else we have lying around the office that we\u2019re hoping to get rid of<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

Now, admittedly, the competition is fierce: we\u2019ve 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\u2019t tell them that we\u2019re going to be renaming the Script Center soon. We\u2019d kind of like to \u2026 surprise \u2026 them with that. No doubt they\u2019ll be thrilled.)<\/P>\n

However, in keeping with Script Center tradition, you don\u2019t 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\u2019ll 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.<\/P>\n

Of course, that does lead to an obvious question: how do you even begin<\/I> to prepare yourself to undergo something as rigorous and demanding as the TechEd Challenge? That\u2019s also an easy one. How do you prepare for the TechEd Challenge? The same way you prepare for any<\/I> of life\u2019s 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.<\/P>\n

Fortunately, we just happened to have a script that does that very thing:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)<\/p>\n
i = 1<\/p>\n
Do Until objExcel.Cells(i, 1).Value = “”\n    If objExcel.Cells(i, 1).Value = “delete” Then\n        Set objRange = objExcel.Cells(i, 1).EntireRow\n        objRange.Delete\n        i = i – 1\n    End If\n    i = i + 1\nLoop\n<\/PRE>\n
Let\u2019s see if we can figure out how this script works. (At the risk of spoiling the suspense, we\u2019ll note that there is<\/I> one plot twist that you\u2019ll need to pay special attention to.) We start out by creating an instance of the Excel.Application<\/B> object and setting the Visible<\/B> property to True; that gives us a running instance of Excel that we can see on screen. After that we call the Open<\/B> method to open the file C:\\Scripts\\Test.xls, then assign the value 1 to a counter variable named i (a variable we\u2019ll use to keep track of the current row in the worksheet). At that point, we\u2019re ready to start whacking rows in the spreadsheet.<\/P>\n
Speaking of which, for educational purposes our spreadsheet looks something like this:<\/P>\"Microsoft \n

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

All we have to do now is figure out how to get from Point A to Point B.<\/P>\n
As highly-trained professionals, the Scripting Guys know that you\u2019re unlikely to get from Point A to Point B unless you start out by doing something<\/I>. Therefore, our first step is to set up a Do Until loop that runs until we encounter a blank cell in column A:<\/P>
Do Until objExcel.Cells(i, 1).Value = “”\n<\/PRE>\n
As you can see, all we\u2019re doing here is checking to see if cell i<\/I>, 1 (row i<\/I>, column 1) is equal to an empty string (\u201c\u201d). If it is<\/I>, then we\u2019re done: that means we\u2019ve looked at all the rows in the spreadsheet (or at least all the rows that contain data). If it\u2019s not<\/I> empty, well, then we have some work to do.<\/P>\n
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<\/I>. If it\u2019s 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<\/I> equal to delete<\/I>? What do we do then? <\/P>\n
Here\u2019s what we do then, we execute this block of code:<\/P>
Set objRange = objExcel.Cells(i, 1).EntireRow\nobjRange.Delete\ni = i – 1\n<\/PRE>\n
In the first line we create a Range<\/B> object that begins in cell i<\/I>, 1 and then proceeds to gobble up the rest of the row as well. (How do we know<\/I> that this range encompasses the entire row? Because we used the EntireRow<\/B> property when we created the range.) In line 2 we then call the Delete<\/B> method and delete the row in question. That\u2019s pretty east, and pretty straightforward.<\/P>\n
The third line in our little code block is the tricky part. Why i = i – 1<\/B>? Having deleted one row in the spreadsheet our thoughts should lgoically turn to the next<\/I> row in the spreadsheet. Shouldn\u2019t this line of code be i = i + <\/B>1 instead?<\/P>\n
Believe it or not, no; it shouldn\u2019t be. And here\u2019s why. <\/P>\n
Suppose we\u2019re on row 2 of the spreadsheet. What happens if we delete this row? That\u2019s 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. <\/P>\n
Of course, there\u2019s nothing wrong with that, but it does lead to one problem: we haven\u2019t checked the value of this new row 2 yet. That\u2019s why we decrement<\/I> 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<\/I> the counter variable, i will be set to 2. Doesn\u2019t that mean we\u2019re going to check row 2 again? Yes, and that\u2019s the point: remember, this is a brand-new row 2, one that we haven\u2019t checked yet.<\/P>\n
Yes, we know: it is<\/I> a little confusing, isn\u2019t 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<\/B> and re-run the script. At that point it should be clear why we need to perform this little trick.<\/P>\n
And that, believe it or not, is all we have to do. When we\u2019re all done the rows marked delete<\/I> will be deleted, and the rows marked keep<\/I> won\u2019t be deleted. Which means that, for once, at least, things really did<\/I> go according to plan.<\/P>\n
And remember: enter the TechEd Challenge<\/B><\/A> while there\u2019s 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 \u2013 let\u2019s see, divide by 12, carry the 2, round up to the nearest dollar \u2026 0 dollars and 0 cents. Needless to say, that means that this might very well be a once-in-a-lifetime opportunity. <\/P>\n
Oh: and don\u2019t forget that, if you get a perfect score, for one day we\u2019ll rename the Script Center after you. That\u2019s also likely to be a once-in-a-lifetime opportunity, especially when TechNet finds out what we\u2019re doing \u2026.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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 Hey, OI. Before we get rolling on today\u2019s column, have we mentioned that time is beginning to run out on the Scripting Guys TechEd Challenge? We have? Oh. Well, […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,49,3,5],"class_list":["post-64593","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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 Hey, OI. Before we get rolling on today\u2019s column, have we mentioned that time is beginning to run out on the Scripting Guys TechEd Challenge? We have? Oh. Well, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64593","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64593"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64593\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64593"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64593"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64593"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}