none
Select a subrange of a filtered range RRS feed

  • 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

Answers

  • 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 Niels Olesen 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,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    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 Niels Olesen Wednesday, July 31, 2013 12:19 PM
    Tuesday, July 30, 2013 3:25 PM
    Answerer
  • 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 Niels Olesen 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 Niels Olesen 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,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    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 Niels Olesen Wednesday, July 31, 2013 12:19 PM
    Tuesday, July 30, 2013 3:25 PM
    Answerer
  • 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 Niels Olesen 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