Copying data from autofilter in vba

Question

I am trying to copy data from cells that have been autofiltered in vba. My code looks like this:

 For Each myArea In myRange.Areas
      For Each rw In myArea.Rows
          strFltrdRng = strFltrdRng & rw.Address & ","
      Next
 Next

 strFltrdRng = Left(strFltrdRng, Len(strFltrdRng) - 1)
 Set myFltrdRange = Range(strFltrdRng)
 myFltrdRange.Copy
 strFltrdRng = ""

 Workbooks(mainwb).Activate
 Workbooks(mainwb).Worksheets("Sheet1").Range("A1").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

But when the variable strFltrdRng is this long:

"$B$2:$H$2,$B$3:$H$3,$B$4:$H$4,$B$5:$H$5,$B$6:$H$6,$B$7:$H$7,$B$8:$H$8,$B$10:$H$10,$B$11:$H$11,$B$12:$H$12,$B$13:$H$13,$B$15:$H$15,$B$17:$H$17,$B$18:$H$18,$B$19:$H$19,$B$20:$H$20,$B$21:$H$21,$B$22:$H$22,$B$23:$H$23,$B$26:$H$26,$B$27:$H$27,$B$28:$H$28,$B$2"

It throws me an error: Method 'Range' of object_Global' failed. However when I shorten the strFltrRng, I am able to copy the data.

Is there any way to solve this problem?

Have your tried working with any of these two solutions instead: http://stackoverflow.com/questions/17531128/copy-paste-calculate-visible-cells-from-one-column-of-a-filtered-table OR http://stackoverflow.com/questions/16039774/excel-vba-copying-and-pasting-visible-table-rows-only? If that's not an option: how much data are we talking about? Is it a table you could possibly `SELECT` from like so: http://stackoverflow.com/questions/2188233/how-to-select-a-subset-of-a-table-with-excel-vba-in-the-manner-of-select-from? — Ralph, Apr 13 '16 at 12:52
I must be missing some relevant info so take it just as a guessing: why not simply go `myRange.Copy` and then `Selection.PasteSpecial ...`? — user3598756, Apr 13 '16 at 13:12

score 0 · Answer 1 · answered Apr 13 '16 at 13:33

Should not be useful (see my above comment) the following "direct" solution:

myRange.Copy
Workbooks(mainwb).Worksheets("Sheet1").Range("A1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I propose the following alternative:

Dim dummyRange As Range
...
Set dummyRange = myRange.Parent.Range("XFD10000") '<== I assume cell "XFD10000" isn't used... As for this range utility, see right below
Set myFltrdRange = dummyRange '<=="dummy" and "helper" range to falsely initialize myFltrdRange and prevent ".Union()." method from failing the first time. it'll be removed from resulting "myFltrdRange" before the ".Copy" method
For Each myArea In myRange.Areas
    For Each rw In myArea.Rows
        Set myFltrdRange = Union(myFltrdRange, rw)
    Next
Next

Intersect(myFltrdRange, myRange).Copy '<== this removes the"dummy" range
Workbooks(mainwb).Worksheets("Sheet1").Range("A1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Copying data from autofilter in vba

1 Answers1