# Select a subrange of a filtered range

• ### Question

• Hi

I'm having a peculiar problem, selecting a sub range of a range, which I in terms have extracted from a worksheet, where I have applied a filter.

My worksheet looks like this:

 num typ id nam TEST 1 a 3 a1 b1 2 a 4 a2 b2 3 a 1 a3 b3 4 a 8 a4 b4 5 b 9 a5 b5 6 b 22 a6 b6 7 b 2 a7 b7 8 b 7 a8 b8 9 a 13 a9 b9 10 a 14 a10 b10 11 b 5 a11 b11 12 b 20 a12 b12 x a 12 a13 b13 x a 15 a14 b14 x a 6 a15 b15 x a 10 a16 b16 x a 11 a17 b17 x b 16 a18 b18 x b 17 a19 b19

I apply a filter to choose only the typ = a and the num being a number (omitting the x'ses. This results in the following:

 num typ id nam TEST 1 a 3 a1 b1 2 a 4 a2 b2 3 a 1 a3 b3 4 a 8 a4 b4 9 a 13 a9 b9 10 a 14 a10 b10

I now apply the VBA script (copy_selection):

```Sub copy_selection()

With Sheets("Sheet1")
With .AutoFilter.Range
Set rngFiltered = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)
End With
End With

With Sheets("Sheet1")
Set rngDestinA = .Cells(26, 1)
Set rngDestinB = .Cells(26, 6)
End With

rngFiltered.Copy Destination:=rngDestinA
rngFiltered.Columns(3).Copy Destination:=rngDestinB

End Sub```
And this results in the following:  1 a 3 a1 3 2 a 4 a2 4 3 a 1 a3 1 4 a 8 a4 8 9 a 13 a9 10 a 14 a10

My question is: Why is the subrange (rngFiltered.Columns(3)) only containing the first four rows. The rngFiltered.Copy method seems to work.  This seems to be independent on the type of filters I apply. First I thought it was because I have two filters (Both on Num and on Typ), but this is not the case

I'd appreciate any help on this

PS - The TEST columns is just for testing that the rngFiltered range in fact only contains the data form the filter range

PPS: I hacked the contents of the script from OssieMac's snippet: http://social.msdn.microsoft.com/Forums/en-US/af91ef81-9908-4db4-a948-1d3ababab8b1/use-autofilter-to-filter-and-copy-the-results-to-a-new-worksheet-vba-updated-for-array.

Thanks for that piece of code

Thanks

Niels

Tuesday, July 30, 2013 2:20 PM

• Some multi-area ranges default to the first area for some properties - .Cells is another that springs to mind. Change your last copy command to this:

Intersect(rngfiltered, rngfiltered.Columns(3).EntireColumn).Copy Destination:=rngDestinB

• Marked as answer by Wednesday, July 31, 2013 12:19 PM
Tuesday, July 30, 2013 3:20 PM
• Refer offline help on columns property of range object:

"When applied to a Range object that's a multiple-area selection, this
property returns columns from only the first area of the range. For example, if
the Range object has two areas — A1:B2 and C3:D4 —
`Selection.Columns.Count` returns 2, not 4. To use this property on a
range that may contain a multiple-area selection, test `Areas.Count`
to determine whether the range contains more than one area. If it does, loop
over each area in the range."

In your case first area is A2:E5 so the columns(3) refers to C2:C5.

Best Regards,
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

• Marked as answer by Wednesday, July 31, 2013 12:19 PM
Tuesday, July 30, 2013 3:25 PM
• Niels,

Leaving the "'.SpecialCells(xlCellTypeVisible)" out of the code the line
rngFiltered.Columns(3).Copy Destination:=rngDestinB
will copy as expected.

Sub copy_selection()
Dim rngDestinA As Range
Dim rngDestinB As Range
Dim rngFiltered As Range

With Sheets("Sheet1")
With .AutoFilter.Range
Set rngFiltered = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With
End With

With Sheets("Sheet1")
Set rngDestinA = .Cells(26, 1)
Set rngDestinB = .Cells(26, 6)
End With

rngFiltered.Copy Destination:=rngDestinA
rngFiltered.Columns(3).Copy Destination:=rngDestinB

End Sub

Jan

• Marked as answer by Wednesday, July 31, 2013 12:19 PM
Wednesday, July 31, 2013 9:43 AM

### All replies

• Some multi-area ranges default to the first area for some properties - .Cells is another that springs to mind. Change your last copy command to this:

Intersect(rngfiltered, rngfiltered.Columns(3).EntireColumn).Copy Destination:=rngDestinB

• Marked as answer by Wednesday, July 31, 2013 12:19 PM
Tuesday, July 30, 2013 3:20 PM
• Refer offline help on columns property of range object:

"When applied to a Range object that's a multiple-area selection, this
property returns columns from only the first area of the range. For example, if
the Range object has two areas — A1:B2 and C3:D4 —
`Selection.Columns.Count` returns 2, not 4. To use this property on a
range that may contain a multiple-area selection, test `Areas.Count`
to determine whether the range contains more than one area. If it does, loop
over each area in the range."

In your case first area is A2:E5 so the columns(3) refers to C2:C5.

Best Regards,
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

• Marked as answer by Wednesday, July 31, 2013 12:19 PM
Tuesday, July 30, 2013 3:25 PM
• Niels,

Leaving the "'.SpecialCells(xlCellTypeVisible)" out of the code the line
rngFiltered.Columns(3).Copy Destination:=rngDestinB
will copy as expected.

Sub copy_selection()
Dim rngDestinA As Range
Dim rngDestinB As Range
Dim rngFiltered As Range

With Sheets("Sheet1")
With .AutoFilter.Range
Set rngFiltered = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With
End With

With Sheets("Sheet1")
Set rngDestinA = .Cells(26, 1)
Set rngDestinB = .Cells(26, 6)
End With

rngFiltered.Copy Destination:=rngDestinA
rngFiltered.Columns(3).Copy Destination:=rngDestinB

End Sub

Jan

• Marked as answer by Wednesday, July 31, 2013 12:19 PM
Wednesday, July 31, 2013 9:43 AM
• Thanks guys.

I ended up using Jan's suggestion, but both Asadulla's and Bernie's replies explained why the problem occurred

Thanks again
Wednesday, July 31, 2013 12:19 PM