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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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?
 
Upvote 0
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.)
 
Upvote 0
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).
 
Upvote 0
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? :)
 
Upvote 0
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)
 
Upvote 0
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! :)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top