{"id":63463,"date":"2007-12-05T01:02:00","date_gmt":"2007-12-05T01:02:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/12\/05\/hey-scripting-guy-how-can-i-cut-a-row-from-one-office-excel-spreadsheet-and-paste-that-row-into-another-spreadsheet\/"},"modified":"2007-12-05T01:02:00","modified_gmt":"2007-12-05T01:02:00","slug":"hey-scripting-guy-how-can-i-cut-a-row-from-one-office-excel-spreadsheet-and-paste-that-row-into-another-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-cut-a-row-from-one-office-excel-spreadsheet-and-paste-that-row-into-another-spreadsheet\/","title":{"rendered":"Hey, Scripting Guy! How Can I Cut a Row From One Office Excel Spreadsheet and Paste That Row Into Another Spreadsheet?"},"content":{"rendered":"

\"Hey, <\/H2>\n

Hey, Scripting Guy! How can I cut a row from an Office Excel spreadsheet and paste it into the first unused row in another Excel spreadsheet?

— NC<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, NC. We apologize if today\u2019s column feels a little damp; as it turns out, those of us in the Seattle area are experiencing another one of the Puget Sound\u2019s \u2026 delightful \u2026 shifts in the weather. Saturday afternoon we got hit with a bucketful of snow; at the Scripting House the snow was piled at least 6 inches high on the railing of the deck. By Sunday morning most of that snow had melted, just in time for another big bucketful to hit. Midway through that<\/I> blizzard the temperature suddenly shot up about 15 degrees; in fact, currently it\u2019s 58 degrees Fahrenheit, which, unofficially, ties the mark for the warmest December 3rd<\/SUP> on record.<\/P>\n

Oh, and once the temperature shot up it began to rain. And rain. And rain. It began raining \u2013 hard \u2013 about 10:00 AM Sunday morning and, as of 2:30 PM Monday afternoon, it hadn\u2019t stopped. As it is, forecasters are predicting that this deluge will break the Seattle record for most rainfall in a single 24-hour period.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. What? No, the record isn\u2019t 147 feet; surprisingly enough, it\u2019s just a little over 5 inches. The truth is, it usually doesn\u2019t rain hard<\/I> in Seattle; it\u2019s just that the misty-style rain that we do<\/I> get never seems to stop. Seattle, despite its reputation, only gets an average of 37 inches of rain per year; that\u2019s less rain per year than Washington, DC, less rain per year that Miami, FL, less rain per year than New York City, less rain per year than Houston, TX. In other words, less rain per year than lots of places.<\/P>\n

Not that any of that is much consolation at the moment, mind you.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

On the bright side, at least there\u2019s not much incentive to go outside and do something, not today anyway. And seeing as how we are<\/I> stuck inside, well, we might try our hand at writing a script that can cut a row from one Microsoft Excel spreadsheet and then paste that row into a second spreadsheet.<\/P>\n

You know, something along the lines of this<\/I> script:<\/P>

Const xlDown = -4121<\/p>\n
‘Open first spreadsheet\nSet objOldExcel = CreateObject(“Excel.Application”)\nobjOldExcel.Visible = True<\/p>\n
Set objOldWorkbook = objOldExcel.Workbooks.Open(“C:\\Scripts\\OldTest.xls”)\nSet objOldWorksheet = objOldWorkbook.Worksheets(“Sheet1”)<\/p>\n
‘Open second spreadsheet\nSet objNewExcel = CreateObject(“Excel.Application”)\nobjNewExcel.Visible = True<\/p>\n
Set objNewWorkbook = objNewExcel.Workbooks.Open(“C:\\Scripts\\NewTest.xls”)\nSet objNewWorksheet = objNewWorkbook.Worksheets(“Sheet1”)<\/p>\n
‘Cut row from the original spreadsheet\nSet objOldRange = objOldWorksheet.Range(“A1”).EntireRow\nobjOldRange.Cut<\/p>\n
‘Find the first unused row in the second spreadsheet<\/p>\n
Set objNewRange = objNewExcel.Range(“A1”)\nobjNewRange.End(xlDown).Activate\nintNewRow = objNewExcel.ActiveCell.Row + 1\nstrNewCell = “A” &  intNewRow\nobjNewExcel.Range(strNewCell).Activate<\/p>\n
‘Paste the data into the second spreadsheet\nobjNewWorksheet.Paste<\/p>\n
‘Delete the row from the original spreadsheet\nobjOldRange.Delete\n<\/PRE>\n
OK, we admit it: this script is a little more complicated than it probably should be. But don\u2019t worry; we\u2019ll see what we can do to help you understand what\u2019s going on. And because the biggest problem here lies in remembering which of the two spreadsheets you\u2019re working with, we\u2019ve even done something very radical and out-of-character for Hey, Scripting Guy!<\/I>: we\u2019ve added a few comments to the code, hopefully helping you to distinguish between the different parts of the script.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. What\u2019s that? Why didn\u2019t we just color code the script? Boy, you guys haven\u2019t done much work with the Web page templates we\u2019re allowed to use, have you? We\u2019ve been trying for more than 3 years just to get boldfacing<\/I> into our code samples. Color coding is something we don\u2019t even bother dreaming about.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
To begin with, we define a constant named xlDown and assign it the odd value -4121; we\u2019ll use that constant when we set out to find the first unused row in worksheet number 2. We then use this block of code to:<\/P>\n\n\n\n\n\n
\n
1.<\/P><\/TD>\n
\n
Create an instance of the Excel.Application object.<\/P><\/TD><\/TR>\n
\n
2.<\/P><\/TD>\n
\n
Set the Visible property to True<\/P><\/TD><\/TR>\n
\n
3.<\/P><\/TD>\n
\n
Open the file C:\\Scripts\\OldTest.xls and bind to the first worksheet in that file<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Here\u2019s the block of code in question:<\/P>
Set objOldExcel = CreateObject(“Excel.Application”)\nobjOldExcel.Visible = True<\/p>\n
Set objOldWorkbook = objOldExcel.Workbooks.Open(“C:\\Scripts\\OldTest.xls”)\nSet objOldWorksheet = objOldWorkbook.Worksheets(“Sheet1”)<\/PRE>\n
OK, that wasn\u2019t too bad, was it? Now we\u2019re going to repeat this process, this time opening a spreadsheet named NewTest.xls and binding to the first worksheet in that file:<\/P>
Set objNewExcel = CreateObject(“Excel.Application”)\nobjNewExcel.Visible = True<\/p>\n
Set objNewWorkbook = objNewExcel.Workbooks.Open(“C:\\Scripts\\NewTest.xls”)\nSet objNewWorksheet = objNewWorkbook.Worksheets(“Sheet1”)<\/PRE>\n
If you were able to follow that, you know that we now have two instances of Excel up and running on screen; one instance uses the object reference objOldExcel, the other uses the object reference objNewExcel. Got that? Good. Then we\u2019re halfway home.<\/P>\n
Our next task is to cut row 1 from OldTest.xls, the spreadsheet that objOldExcel refers to. That\u2019s what we do here:<\/P>
Set objOldRange = objOldWorksheet.Range(“A1”).EntireRow\nobjOldRange.Cut\n<\/PRE>\n
Come to think of it, that doesn\u2019t look all that bad, either. (Does it?) In the first line, we\u2019re simply creating an instance of Excel\u2019s Range<\/B> object. Because we want this range to encompass row 1 we start the range in cell A1<\/B>, then tack on the EntireRow<\/B> property to make sure that all the cells in that row are included in the range.<\/P>\n
And then, in line 2, we call the Cut<\/B> method on that range to cut row 1 from the first spreadsheet.<\/P>\n
Well, kind of. As it turns out, the Cut method really functions more like the Copy method: it copies the information to the Clipboard, just like you would expect it to. What it doesn\u2019t<\/I> do, however, is delete that information from the original spreadsheet. In other words, we have row 1 on the Clipboard, just like we want. However, we also still have row 1 in the original spreadsheet, just like we don\u2019t<\/I> want. Could this day get any better? <\/P>\n
But, hey, don\u2019t worry: we can\u2019t do much about the weather, but we can<\/I> (and will) show you a way to work around the Cut-doesn\u2019t-actually-cut problem.<\/P>\n
That brings us to this block of code:<\/P>
Set objNewRange = objNewExcel.Range(“A1”)\nobjNewRange.End(xlDown).Activate\nintNewRow = objNewExcel.ActiveCell.Row + 1\nstrNewCell = “A” &  intNewRow\nobjNewExcel.Range(strNewCell).Activate\n<\/PRE>\n
This is the block of code that enables us to locate the first unused row in our second spreadsheet. (Note that this approach is predicated on the idea that the first blank row in column A is also the first unused row in the spreadsheet.) To locate the first unused row (which will also be the location where we want to paste the row we just cut), we start out by creating a Range object that consists solely of cell A1:<\/P>
Set objNewRange = objExcel.Range(“A1”)\n<\/PRE>\n
Why do we do that? Well, to find the first unused row we\u2019re going to mimic the behavior of Ctrl+Down Arrow<\/B>; if you press Ctrl+Down Arrow in Excel the cursor will pop down to the last cell in the column that contains data. Before we can do that, however, we need to have the cursor in cell A1; this is one simple way to do that.<\/P>\n
Our next step is to actually go ahead and mimic the behavior of Ctrl+Down Arrow<\/B>, something that requires just one line of code:<\/P>
objNewRange.End(xlDown).Activate\n<\/PRE>\n
Here we\u2019re using both the End<\/B> property and the Activate<\/B> method to move to the end of the column (that is, the last cell in the column that contains data). To move the cursor to the end of the column we set the value of End to the constant xlDown; that tells Excel that we want to go to the \u201cend\u201d of the column. (Technically, of course, we\u2019re just moving down the column until we encounter a blank cell.) After making that move we then call the Activate method to make that cell (whatever it is) the active cell. <\/P>\n
That\u2019s great, except for one thing: we\u2019re still not in the first unused row in the spreadsheet. In fact, the first unused row is the row directly beneath us. How do we get the cursor down one more row?<\/P>\n
To tell you the truth there are a couple different ways we could do this. For better or worse we chose this method:<\/P>
intNewRow = objNewExcel.ActiveCell.Row + 1\nstrNewCell = “A” &  intNewRow\nobjNewExcel.Range(strNewCell).Activate\n<\/PRE>\n
Here we\u2019re assigning a value to a variable named intNewRow; the value we assign will be the row number of the active cell plus 1. Why \u201cplus 1?\u201d Because we don\u2019t want to be in the current row, we want to be down in the next<\/I> row.<\/P>\n
We then assign a value to another new variable, strNewCell; this variable gets assigned the letter A<\/B> plus the value of intNewRow. In other words, if the first unused cell in column A is cell A6 then strNewCell gets assigned the value A6<\/B>. To move the cursor to this new cell we create a range consisting of this one cell and call the Activate method. Our cursor is now in a blank cell, we know the address of this cell (A6), and we\u2019re ready to paste in some data.<\/P>\n
Which, fortunately, we can do with just a single line of code:<\/P>
objNewWorksheet.Paste\n<\/PRE>\n
Now we just have one problem left: the data we pasted into the new spreadsheet is still sitting there in the original spreadsheet; as we noted earlier, the data doesn\u2019t actually get cut<\/I> from its original location. But that\u2019s OK. Seeing as how we already have a Range object that refers to this data all we have to do is call the Delete<\/B> method and the data will<\/I> get deleted from the original spreadsheet:<\/P>
objOldRange.Delete\n<\/PRE>\n
Yes, it\u2019s a little weird and a little complicated. But it works. And if you try running the script a few times you\u2019ll soon begin to catch on to how<\/I> it works.<\/P>\n
We hope that helps, NC. We also hope that none of you are worrying about the Scripting Guys. Yes, it\u2019s still raining here, and yes there are reports of floods throughout the Seattle area. But remember, the Scripting Guys work for Microsoft: we\u2019re perfectly safe sitting up in our ivory towers, detached and isolated from the real world.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I cut a row from an Office Excel spreadsheet and paste it into the first unused row in another Excel spreadsheet?— NC Hey, NC. We apologize if today\u2019s column feels a little damp; as it turns out, those of us in the Seattle area are experiencing another one of the […]<\/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,711,48,3,5],"class_list":["post-63463","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-microsoft-excel","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I cut a row from an Office Excel spreadsheet and paste it into the first unused row in another Excel spreadsheet?— NC Hey, NC. We apologize if today\u2019s column feels a little damp; as it turns out, those of us in the Seattle area are experiencing another one of the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63463","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=63463"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63463\/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=63463"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63463"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63463"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}