Copy and paste tries to paste the entire spreadsheet

Hi Carl,

We need to gather additional information to have a better view of this concern. Kindly answer the following questions:

• Is this the first time that you encountered this concern?
• Did you notice any changes to the computer prior to this issue?
• Is this happening to a specific Excel file?
• What troubleshooting steps have you tried so far?
• Does your computer connected to a domain?
• What is the current version and build number of your Office? To determine which version of Office you are using, click this link.

In the meantime, we suggest following the steps on how to copy the only visible cells in Excel provided in this article. Once done, check if you will still encounter the same issue. If so, please follow the steps below:

Step 1: Start your Excel app in Safe Mode.

Doing so we will be able to identify if there are add-ins that are causing the issue. Here's the link where you can find the instructions on how to do so. Once in Safe Mode, check if you will experience the same issue.

Step 2: Perform an online repair via Control Panel.

Please post back with the result for further assistance.

Try selecting the filtered current region using shortcut   Ctrl + Shift + *

Or just Ctrl + *  on numpad

Gord

Sorry but I had other issues come up that shifted my focus.  It is not the first time this has happened, but I hadn't paid much attention in the past.  There were no changes to the computer and it appears to be very specific to Excel, but NOT to a specific file.  Computer is not connected to a domain, and there are no add-ins so nothing that would be causing the problem.  I can't get to the version and build number because my 365 trial has since expired and I haven't decided if I am going to subscribe.  This problem slowed me down and created great headaches for me and LibreOffice just worked.

I have tried to recreate the problem a variety of ways but I still haven't figured out what causes it so can't faithfully recreate the problem.  But...it MAY be caused by autofill of column data.  In one case I was trying to convert a column of numbers formatted as text to numbers.  Nothing else worked so I found a workaround where I entered the number 1 into a cell, copied it, then pasted that into the column of text numbers using the Multiply option in paste special.  This worked to generate numbers from the text, but I believe it also applied the multiply function to the empty cells below the data, all the way to the bottom of the sheet.

Again, I don't know if that was the entire problem and can no longer test it.  I may try in Office 2007 on my older computer and will report back if I find out anything more.  But that computer is fairly slow with such a large data set.  I'd test it on my new computer but I can't install 2007 on the new computer - even though the Microsoft documentation says I should be able to.

Thanks.  The problem, I have discovered, is that there IS data all the way to the bottom of the spreadsheet in at least one column, so copying the "current region" of data will select the entire sheet.  I have to just select the 20-30k rows that have actual data, and that is tiresome since the only reliable way I've found to do that is to click and drag the rows from the bottom to the top, copy, then paste that data into a new sheet.  Doing that is quite slow.

Unfortunately the problem recurs frequently enough to slow me down considerably.  If I encounter the problem again, I'll try other ways to select/copy/paste just the relevant rows of data rather than having to drag to select them.

I'm still interested in suggestions if anyone has any.  Thanks again!

What do you mean by "actual data"?

Am I to assume that anything else is not data?

Perhaps you need to reset the used range on the sheet.

Select all rows below your data and delete(do not clear contents)  then select all columns to the right of your data and delete.

Save and close then re-open then select current region.

Gord

What I mean by "actual data" is the data that is truly a part of the data set I'm working with.  Excel fills one of my columns below the "actual data" with zeros or some other data (that may or may not be visible) all the way to the bottom of the sheet (let's say for argument's sake from row 30,000, where the last row of real data is, to row 1,048,576).  The spreadsheet then acts as if I have 1,048,576 rows of data (unfortunately, because of what Excel is doing I suddenly DO have 1,048,576 rows of data).  Until I get rid of the "fake" data (like "fake" news, LOL!), most things I try to do are slow and sometimes fill in further columns of data and play havoc with formulas, vlookups and other things I need to do.

If this happened once every week or so I could live with your suggestion and probably wouldn't have bothered posting here.  Better yet is what I did, which was to select the rows of data and copy and paste to a new sheet.  The columns to the right of my data are never the problem.  But when it happens 5 or 6 times a day it's annoying and time consuming.

I really like certain of the features of Excel but I can't afford to be slowed by a problem like this.  That's why I'd love to get to the bottom of it.  Again, I'll post more if I can dig up some time to explore it more.

You state that Excel is filling the cells to the last row.

Excel will fill the number of cells you tell it to.

Personally I would be investigating why these cells are being filled with zeros and hidden data, which are most likely line-feeds.

Where are the data come from?

Suggest you figure out how to control the infeed.

Gord

Well that, in fact, is exactly my question.  Why is Excel filling in these cells?  I didn't tell it to continue an operation to the bottom of the sheet, but when working in such large data sets you need to use short cuts (e.g. double clicking or control-clicking the lower right corner of a cell to fill to the bottom of the data set (but not the bottom of the sheet).  Or using Paste Special.

For at least one situation, where I'm using the Paste Special function to multiply by 1, I assumed it would multiply all of the cells with a value in them and stop at that.  But it appears to be continuing past the cells that have data and multiplying even blank cells by 1.  Not sure how you calculate the equation 1 times nothing (1 x =? ).  (note it's 1 multiplied by nothing, not 1 x 0).  To multiply any number by a blank cell and get the result of 0 suggests an error in the calculation.  I assume Excel counts a blank cell as a zero, but that's not accurate in my mind.  Blank is definitely NOT = 0 in a database (I'm a database specialist).

I wonder if someone would try to replicate what I'm seeing and report back.  Curious if anyone else has this experience.  I'd also love to hear what the intended behavior is of the Paste Special Multiply tool, and how Excel handles a blank cell.  Many of the "fill" commands stop when a cell they are operating on is blank, which suggests at least in some cases Excel considers a blank cell a true blank, not a 0.

When you d-click on a cell, Excel will stop filling when it reaches the bottom of the data range in an adjacent column.

If there is no data in an adjacent column, Excel will fill to the bottom of the column.

Suggest in latter case you do not select entire columns as paste range.

Also, enter 0 in a cell.   Copy the cell and paste special>multiply or add to any cell and you will get 0

Gord

Regarding your last sentence, thanks for mentioning that.  That is my experience as well, so now I wonder why it's behaving that way. It is my contention that any mathematical operation, when done against nothing, should produce nothing as a result.  There isn't, in fact, a mathematical operation in that case.  One times nothing is nothing, not zero.  There is an important difference between blank and zero.  And if Excel worked that way, the problem wouldn't exist.  But Excel is operating as if the blank cell contains a zero.   

Given the number of data rows, I can't spend the time to manually drag to the bottom of the range and paste special that way.  I'd never get my work done.  When I copy and paste special, I do that operation on an entire column for that very reason.  I'll try a formula approach and that may resolve this particular part of my problem.  But I don't think this is the only situation where the column gets filled to the bottom.

I will also look for alternative ways to select a column of data to do an operation like this.  I might be able to find a workaround.  But I'm a bit behind and won't have a ton of time to look into it.  And that may mean I don't use Excel.  For at least this current set of files I will have to work in LibreOffice instead of Excel, as so far it seems to work fine in LibreOffice.

Now, I know you, Gord, aren't the person I need to be presenting this to, but I'm hoping someone from MS (e.g. the first reply) is paying attention.  Fat chance of that though I suppose.

Anyway, thank you for sticking with me on this and providing ideas.