Copy/Paste visible cells only

[theLama]

How do I set Excel to default to copy/paste only visible cells? I know how to use Edit/Goto/Special/Visible Cells Only, but want to set defaults to do this without the additional steps.
Thank you,

 

[brian.wethington]

as far as I am aware it is not possible to set as a default (Someone may set me straight on this though). I do know that you can create some VBA to do this and tie it to a button in the custom toolbar.

Sub Test()

Selection.SpecialCells(xlCellTypeVisible).Copy

End Sub

This code will copy only visible cells of the selected range and then you can paste where you need.

Hope this helps!

 

[theLama]

Copy Paste

Brian/All,
Excel does default to copy/paste on my machine, not on my colleague's. We looked in the Options boxes to try to find the difference, but found nothing.
It can be done, we just don't know how.

 

[Datsmart]

My Excel 2003 copies only visible cells. Don't know if it is a version thing or not. I will try my version 2000 when I get home tonight.

 

[theLama]

Excel 2003

Hi John,
My colleague is using Excel 2003. He sent me the file and I can copy/paste visible cells without preceding with Goto/Special/Visible.
Not sure what's happening. If I find out something from my IT Dept, I'll post it here.
Thank you for your help.

 

[brian.wethington]

Okay, I think there could possibly be some confusion.

It will depend on how the cells is hidden as to whether Excel defaults to copy visible/not visible. For example, if you physcally hide row 3 and you try to copy and paste the range A2:A4 you will copy Paste that range. If row 3 is hidden due to an autofilter then it will copy A2 and A4 without A3. I could be wrong, but as far as I am aware, there is no way to change that default.

That is where my short VBA comes in handy, but from the sound of the OP's explanation this is not what is occurring here, so I guess there must be some sort of way to change that default.

Edit-

BTW, one quick aside. If the row 3 is physically hidden and you copy paste entire rows 2:4 it will paste all 3 rows as well but will automatically hide row 3. Again this is default on my machine though.

 

[Datsmart]

Yes, Brian, I think you nailed it.

I was hiding my rows via AutoFilter, not by Format | Hide.
It works exactly like you say, a range copy in AutoFilter does NOT copy hidden rows.
Rows hidden by Format DO copy.

 

[theLama]

Clarification

I'm sorry I didn't specify this in my initial post. This is occuring on rows hidden using the AutoFilter function. That is why I'm perplexed by the problem (I've never seen it before). He is on Excel 2003 as am I.

 

[Datsmart]

How is this copying taking place?
Manually or by code? If by code, please post it.

 

[brian.wethington]

I was expecting this to remain a problem as I said before in my last post I don't think that the issue was the same so I did some testing.

I have now tested this on 13 machines. I am not able to produce one that defaults to copying visible and not visible alike when autofiltered. I have looked in many options, I have tried to determine if there was possibly a VBA approach that would change an option like this but I am finding nothing. Therefore I am still unaware of any way to change the default. I just know someone out there knows of a way.

Does this happen to your colleague in only this specific book? Or is it absolutely every book he has?

Perhaps my previously posted code assigned to a customized toolbar button would be a temporary band-aid. It would work like the copy button only it would only copy visible cells. Just highlight the range and click the button. If your colleague is a person that perfers keyboard shortcuts to buttons you could assign a keyboard shortcut to the code.