Thread: Autofiltering, copying cells and specialcells

Ok, I've tried recording a macro, doing what I want with the difficult dataset and get the error

Microsoft Office Excel cannot create or use the data range reference because it is too complex. Try one of the following:

• Use data that can be selected in one contiguous rectangle
• Use data from the same sheet

I guess I need a different method of achieving my goal - although I have no idea why this file is anymore complex than the one that works!

Is there a better/more stable method of achieving my goal?

Thanks

Thanks for your reply Leith,

I have tried replacing my code with yours, but I'm still getting the same error message. I believe it's because of the disjointed nature of the data I am trying to copy. Is there a less memory intensive, or complex, method of moving the result of the filter to a different workbook?

Thanks

I'm not sure if it will help you understand or not, but my data has over 25000 rows and I'm filtering out about half of it. Hence alot of the rows that have survived the filter were surrounded by rows that haven't, making the final data very disjointed in sequence. For example the first 20 rows to survive the cull are:

2,4,6,7,9,11,12,14,16,17,19,21,22,24,26,27,29,31,32,34

So I can understand why Excel finds this too complex to copy. Unfortunately what I don't understand is what to do about it!

Sorry, you replied whilst I was writing my last post!

Sorting data is a good idea. I'll let you know...

Sort the data based on the column that you are filtering, so that all the data is still contiguous after you run your filter, then it shouold copy and paste without the error using Leith's code. You can add the sort code above your filter/copy code, so it is all accomplished programatically.

Sorry. You posted your response while I was typing MY reply.

Have a good one.

Hutch, I can't work out if you are being ironic or criticising my grammar. Either way, thanks for your help

I've managed to filter the data, which is preventing the error I was getting... However, this in turn has caused a new problem. I have to apply the filter twice, the rsult fo the first one gets copied to Sheet2 and the result of the second goes to Sheet3. I'm using this code

Dim Rng As Range
            .Range("A1").Value = "code"
            
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=Range("A1") _
                , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange Range("A2", Cells(Rows.Count, "L").End(xlUp))
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            
            ActiveSheet.AutoFilterMode = False
            Set Rng = Range("A1", Cells(Rows.Count, "L").End(xlUp))
            Rng.AutoFilter Field:=1, Criteria1:="$GF3DW"
            Rng.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=Worksheets("Sheet2").Range("A1")
            
            ActiveSheet.AutoFilterMode = False
            Rng.AutoFilter Field:=1, Criteria1:="$GFDTA"
            Rng.AutoFilter Field:=9, Criteria1:=1
            Rng.SpecialCells(xlCellTypeVisible).Copy _
                Destination:=Worksheets("Sheet3").Range("A1")

The code going to Sheet2 works great. But nothing is being copied to Sheet3 - I can see it is being filtered correctly, so t's something with the copy and paste. Does one of the parameters have to be reset after the first time, or is there another reason you can see?

Also, my code for sorting the data looks a little ugly, is there a more elegent method of coding this?

Thanks again

It wasn't making fun, just observing the ironic.

The only issue I can see is that you appear to be filtering on two fields when you copy to sheet 3, but the syntax doesn't look corect. It looks like the 2nd filter would overwrite the first.

I have two suggestions:
1. Record the filter process again, and then study the results.
2. Debug the macro by stepping through it step-by-step using F8, with your VB Editor and your Excel window both visible, and you can see what's happening in each step of your macro.

Hope that helps.

Thanks Hutch, I seem to have it working now. Although I admit I had to go back to my original syntax for the copying. Still not sure why.

Thanks again