Simple cut & paste with filters on - having problems

DebbyG

Board Regular
Joined
Jun 8, 2004
Messages
110
Sorry for this simple question, but while I have filters on and I copy or cut & paste the data from the filtered range to a new column, it is copying all that between the filtered rows, and not the data shown with the filter.

Am I being dumb today and missing something :oops: or do I have problems with my version of excel??

Many thanks



DebbyG
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,972
This will copy only the rows that are in the range specified AND are visible:
Range("A1:H10000").SpecialCells(xlCellTypeVisible).Copy

Does it help?
Bye.
 

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
994
You may need to highlight the cells you would like to copy, press ctrl G, click on special, and then click on visible cells only (on the right) press ok. then press ctrl c (to copy) and then paste it where you need.

hope this helps
Sam
 

DebbyG

Board Regular
Joined
Jun 8, 2004
Messages
110
Thanks for your help

I have tried both options and Excel is saying it doesnt recognise the word SpecialCells in the forumla and when I use the paste function from Ctrl G, it pastes only the first 5 rows !!

I'm sure it's something I'm doing so will keep at it...

Many thanks
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,972
Sorry DebbyG,
you wrote "it is copying all that. . . " but probably you ment "it is pasting all that. . . ".

When filter is On, what you copy with Contr-c is what you see; but if you paste this data on a "column" of the filtered rows, the data will be pasted on contiguos rows, including those that are hidden.

If you wish to copy some of the columns of the shown data, you should use formulas to be set on the new columns, so that when you apply filter they are already there.
If you tell us "what" you need to accomplish, in addition to "how" you are trying doing it, maybe we can help more.

Bye,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,466
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top