Copying a row with Autofilter and hidden columns

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
This feels like it should be a really straightforward question, but I haven't found an answer when searching.

One of my colleagues has a spreadsheet. It has hidden columns (to match Group format), and uses Autofilter. Today, with the Autofilter active, she tried to copy a row and paste it elsewhere - and got an error message that the command can't be used with multiple selections.

It seems to be something to do with Excel only copying the visible cells. With just hidden columns, or just Autofilter, everything is fine. But not with both.

The obvious workaround is to not have active filters when copying - which is okay, but not ideal. The other thought I had was to make the hidden columns really thin instead of hidden - again, not ideal.

Any better ideas?
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Emma

I don't seem to suffer any probs when I have a column hidden (and autofilter active) - are you sure it's down to these two circumstances?
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
Hi Richard.

My test data is:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Head1</TD><TD>Head2</TD><TD>Head3</TD><TD>Head4</TD><TD>Head5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>B</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>B</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

If I then hide column C, and filter column A for just As, and copy row 2, then try to paste in row 6, I get "that command cannot be used on multiple selections"

(Using XL2003 btw.)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Ah, that's the paste operation that's failing rather than the copy - you would need to remove Autofilter to do the paste (or do it on another sheet which doesn't have autofilter on, or do it on past of the worksheet that doesn't have hidden columns).
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023

ADVERTISEMENT

Richard - yeah, it's the paste that's failing, but it's because the copy bit is changed to just visible cells. Normally with a hidden column, copying an entire row will select the entire row - I don't know why having autofilter active makes it copy just visible cells.

Just wondered if there was a "Copy the whole bloomin' row like I asked in the first place" function? :)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
You may be able to assign the entire row value to a variant like as in the following:

Code:
Dim v
 
v = [A2:D2].Value   'assumes row 2 is visible!
 
'plonk it somewhere else:
 
[G1].Resize(,4).Value = v

But I agree - it would be nice if it copied everything unless you specified only visible cells (like I seem to remember it worked in xl97)
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
I suspect that the effort I would need to put into error-trapping code like that would probably outweigh the inconvenience of taking the filter off...

Thanks for your help - it's really nice to know that when I say "there's no other easy way round this", it's actually true! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,464
Messages
5,601,810
Members
414,477
Latest member
Ben2000

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