Hey, Scripting Guy! How Can I Cut a Row From One Office Excel Spreadsheet and Paste That Row Into Another Spreadsheet?

ScriptingGuy1

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’s column feels a little damp; as it turns out, those of us in the Seattle area are experiencing another one of the Puget Sound’s … delightful … 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 blizzard the temperature suddenly shot up about 15 degrees; in fact, currently it’s 58 degrees Fahrenheit, which, unofficially, ties the mark for the warmest December 3^rd on record.

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

On the bright side, at least there’s not much incentive to go outside and do something, not today anyway. And seeing as how we are 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.

You know, something along the lines of this script:

Const xlDown = -4121

‘Open first spreadsheet
Set objOldExcel = CreateObject(“Excel.Application”)
objOldExcel.Visible = True
Set objOldWorkbook = objOldExcel.Workbooks.Open(“C:\Scripts\OldTest.xls”)
Set objOldWorksheet = objOldWorkbook.Worksheets(“Sheet1”)
‘Open second spreadsheet
Set objNewExcel = CreateObject(“Excel.Application”)
objNewExcel.Visible = True
Set objNewWorkbook = objNewExcel.Workbooks.Open(“C:\Scripts\NewTest.xls”)
Set objNewWorksheet = objNewWorkbook.Worksheets(“Sheet1”)
‘Cut row from the original spreadsheet
Set objOldRange = objOldWorksheet.Range(“A1”).EntireRow
objOldRange.Cut
‘Find the first unused row in the second spreadsheet
Set objNewRange = objNewExcel.Range(“A1”)
objNewRange.End(xlDown).Activate
intNewRow = objNewExcel.ActiveCell.Row + 1
strNewCell = “A” &  intNewRow
objNewExcel.Range(strNewCell).Activate
‘Paste the data into the second spreadsheet
objNewWorksheet.Paste
‘Delete the row from the original spreadsheet
objOldRange.Delete

OK, we admit it: this script is a little more complicated than it probably should be. But don’t worry; we’ll see what we can do to help you understand what’s going on. And because the biggest problem here lies in remembering which of the two spreadsheets you’re working with, we’ve even done something very radical and out-of-character for Hey, Scripting Guy!: we’ve added a few comments to the code, hopefully helping you to distinguish between the different parts of the script.

To begin with, we define a constant named xlDown and assign it the odd value -4121; we’ll 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:

Here’s the block of code in question:

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

Set objOldWorkbook = objOldExcel.Workbooks.Open(“C:\Scripts\OldTest.xls”)
Set objOldWorksheet = objOldWorkbook.Worksheets(“Sheet1”)

OK, that wasn’t too bad, was it? Now we’re going to repeat this process, this time opening a spreadsheet named NewTest.xls and binding to the first worksheet in that file:

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

Set objNewWorkbook = objNewExcel.Workbooks.Open(“C:\Scripts\NewTest.xls”)
Set objNewWorksheet = objNewWorkbook.Worksheets(“Sheet1”)

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’re halfway home.

Our next task is to cut row 1 from OldTest.xls, the spreadsheet that objOldExcel refers to. That’s what we do here:

Set objOldRange = objOldWorksheet.Range(“A1”).EntireRow
objOldRange.Cut

Come to think of it, that doesn’t look all that bad, either. (Does it?) In the first line, we’re simply creating an instance of Excel’s Range object. Because we want this range to encompass row 1 we start the range in cell A1, then tack on the EntireRow property to make sure that all the cells in that row are included in the range.

And then, in line 2, we call the Cut method on that range to cut row 1 from the first spreadsheet.

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’t 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’t want. Could this day get any better?

But, hey, don’t worry: we can’t do much about the weather, but we can (and will) show you a way to work around the Cut-doesn’t-actually-cut problem.

That brings us to this block of code:

Set objNewRange = objNewExcel.Range(“A1”)
objNewRange.End(xlDown).Activate
intNewRow = objNewExcel.ActiveCell.Row + 1
strNewCell = “A” &  intNewRow
objNewExcel.Range(strNewCell).Activate

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:

Set objNewRange = objExcel.Range(“A1”)

Why do we do that? Well, to find the first unused row we’re going to mimic the behavior of Ctrl+Down Arrow; 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.

Our next step is to actually go ahead and mimic the behavior of Ctrl+Down Arrow, something that requires just one line of code:

objNewRange.End(xlDown).Activate

Here we’re using both the End property and the Activate 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 “end” of the column. (Technically, of course, we’re 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.

That’s great, except for one thing: we’re 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?

To tell you the truth there are a couple different ways we could do this. For better or worse we chose this method:

intNewRow = objNewExcel.ActiveCell.Row + 1
strNewCell = “A” &  intNewRow
objNewExcel.Range(strNewCell).Activate

Here we’re assigning a value to a variable named intNewRow; the value we assign will be the row number of the active cell plus 1. Why “plus 1?” Because we don’t want to be in the current row, we want to be down in the next row.

We then assign a value to another new variable, strNewCell; this variable gets assigned the letter A 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. 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’re ready to paste in some data.

Which, fortunately, we can do with just a single line of code:

objNewWorksheet.Paste

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’t actually get cut from its original location. But that’s OK. Seeing as how we already have a Range object that refers to this data all we have to do is call the Delete method and the data will get deleted from the original spreadsheet:

objOldRange.Delete

Yes, it’s a little weird and a little complicated. But it works. And if you try running the script a few times you’ll soon begin to catch on to how it works.

We hope that helps, NC. We also hope that none of you are worrying about the Scripting Guys. Yes, it’s still raining here, and yes there are reports of floods throughout the Seattle area. But remember, the Scripting Guys work for Microsoft: we’re perfectly safe sitting up in our ivory towers, detached and isolated from the real world.

ScriptingGuy1

Follow