Copying and Pasting While Filtered

adoy73

New Member
Joined
Mar 14, 2006
Messages
46
Hi,

I use filters a lot at my work. One thing I've noticed is that if I have something filtered and want to copy and paste only what I see from one column to another column right next to it, it will copy what is filtered, but then paste into the unfiltered portion.

Is there some way to copy only what I see when it's filtered, THEN paste only onto the adjancent cells which is also filtered?

For example let's say i have each of the number below in a cell (Fig 1). And then I hide rows 2 -3, so only 1 and 4 is showingI only see 1 & 4 in column A. I want to copy and paste 1 and 4 to the next column over one row so it looks like Fig. 2 when unhidden. Not like Fig. 3.

Fig. 1

1
2
3
4

Fig. 2

1 1
2
3
4 4

Fig. 3
1 1
2 4
3
4
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,027
Office Version
  1. 365
1) Filter column A
2) Select B2:B100 (through the end of your data) and press alt + ; (to select visible cells only)
3) type =A2 and press CTRL+ENTER
4) remove your filter
5) If desired, copy & paste values from column B
 

adoy73

New Member
Joined
Mar 14, 2006
Messages
46
Thanks for the reply. I already use that method. It definitely works, but can be a bit cumbersome. Anyone else have a quicker method?
 

wsjackman

Well-known Member
Joined
Jun 19, 2008
Messages
2,465
refering to post 3
Nope

actually people have macros to do this. the macros can be quite cumbersome depending on the details of what is wanted.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,611
Messages
5,765,414
Members
425,285
Latest member
andypandypoo

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