copy hidden columns too

[backyardfun]

Here is a piece of code i was given that will copy an entire row but not the hidden columns. What changes would I need to make to make it copy the hidden columns as well. Thanks.

Please Login or Register  to view this content.

[HaHoBe]

Hi, backyardfun,

copying the visible range

Please Login or Register  to view this content.

This should copy the full range

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I added a With-Statement in the upper part of the code.

Ciao,
Holger

[backyardfun]

I get a compile error, expected end with.

[HaHoBe]

Hi, backyardfun,

Please Login or Register  to view this content.

Ciao,
Holger

[backyardfun]

It does not give me an error but it is not copying and pasting all of my data. It is pasting the headers in row 2 beginning in column B on "buildlist" sheet. I removed the +1 for the columns which fixed that part. Below, I included the code that comes right before the code you provided me. This code queries the rows to be copied that meet the date. This data resides in rows 3-X with a header in line 2. I need it to copy all rows and all hidden columns that meet that date. Thanks.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[HaHoBe]

Hi, backyardfun,

maybe attach a sample workbook for testing instead of revealing more code with named ranges.

Ciao,
Holger

[backyardfun]

[Here is a spreadsheet and my current code in macro Test. As you can see, I unhide rows but do not want to have to do that. Thanks a million!

[HaHoBe]

Hi, backyardfun,

the title of this thread is quite irritating as any hidden columns are made visible in the course of the macro before copying and hidden again at the end.

Please Login or Register  to view this content.

Code works on your sample with Excel2013 on my local system.

Ciao,
Holger

[backyardfun]

I know that the current one works but I do not want to have to unhide and hide the columns. The columns that need to be visible are always changing and I do not want to have to constantly change the column values in the macro.

[HaHoBe]

Hi, backyardfun,

maybe just take a second to reflect on what the command SpecialCells(xlCellTypeVisible) does or look it up in the VBA-Help. You would need a different approach by creating a new range object based on looping thorugh the visible cells in the criteria column as only that would allow the whole area of a row to be added to the range to copy.

And this to my opinion is an excellent example of the advantages of the Advanced filter over the Autofilter and all it´s limitations.

Ciao,
Holger