Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Regions with Filters/Hidden Columns
Hi, I'm trying to copy a region which is has Hidden Columns. For example, in my sheet, column C is hidden, and I'm trying to copy A1:E5 into H1:L5. This works fine. The problem occurs when I filter by a particular column. (Say A). Now, when I copy A1:E5 into H1:L5, it's only copying 4 columns (although it is copying all the rows). Is there any way to avoid this? What am I doing incorrectly? Here's the actual excerpt of my code. --------------- Dim tSheet As Worksheet Dim sSheet As Worksheet Dim sCol As Long Dim sRow As Long Set tSheet = Worksheets("B") Set sSheet = Worksheets("I") sCol = 13 sRow = 4 sSheet.Range(sSheet.Cells(6, 2), sSheet.Cells(2505, 38)).Copy With tSheet Range(.Cells(sRow + 1, sCol + 1), .Cells(sRow + 2500, sCol + 37)).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False, Transpose:=False End With --------------- Specifically, I'm ending up with 37 columns of data when I don't filter, and 36 columns when I do filter (presumably because of the 1 hidden column). Thanks in advance, Scott -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=573831 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Regions with Filters/Hidden Columns
I didn't bother quoting everything, just one addendum. I was mistaken when I said it was copying all the rows when I filtered. It is only copying the visible range (the rest of the data was there from before I filtered). So basically, I would like to copy the entire range (not just what is visible) when it is filtered by some criteria. Any ideas? Scott -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=573831 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why do filters un-hide my hidden rows in Excel? | Excel Discussion (Misc queries) | |||
Copying worksheets with formulas in hidden columns | Excel Worksheet Functions | |||
How do I avoid copying data from hidden rows/columns? | Excel Discussion (Misc queries) | |||
How to find non-blank cell values "hidden" under merged regions? | Excel Discussion (Misc queries) | |||
Hidden Columns No Longer Hidden after Copying Worksheet? | Excel Discussion (Misc queries) |