Cut Paste data from excel sheet using Autofilter in Excel VBA

Question

Current i have an excel with roughly 200000+ records and i need to filter data based on a column. The column has around 5 values and i need to filter out 2 values in one sheet and the rest 3 to remain in the same sheet.

Now instead of using cell by cell comparison to check whether the value of the cell falls in any of the above 2 values and then cut paste the row into another sheet. This wouldn't work with 200k+ records and simply hangs,.

Instead am planning to take the auto filter method. I tried using the 'Record macro' feature, but the problem is that it gives me some error like

"Excel cannot create or use the data range reference because its too complex.Try one of the following Use data that can be selected in rectangle Use data from the same sheet"

Moreover how to copy paste only the filtered values to another sheet? If I try to copy paste directly or special paste as 'values' then also even the hidden rows get copy pasted.

Below is the macro code i have been tampering around with

    Sub Macro34()
    '
    ' Macro34 Macro
    '

    '
        Rows("1:1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$T$81335").AutoFilter Field:=6, Criteria1:="=242", _
            Operator:=xlOr, Criteria2:="=244"
        Cells.Select
        Selection.Copy
        ActiveWindow.SmallScroll Down:=21
        Sheets("Sheet2").Select
        ActiveWindow.SmallScroll Down:=-18
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Selection.ClearContents
        Range("A1").Select
        Sheets("Sheet1").Select
        Selection.Copy
        Sheets("Sheet2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet2").Select
        ActiveWindow.SmallScroll Down:=93
        Sheets("Sheet1").Select
        ActiveWindow.SmallScroll Down:=-9
        ActiveWindow.ScrollRow = 1
        Rows("1:1").Select
        Application.CutCopyMode = False
        Selection.AutoFilter
    End Sub

There might be some junk lines of code above as its generated using the 'record macro' feature.

Could someone please help me. The problem is the amount of data present in excel. Cant excel not handle this much data in VBA? Am using Excel 2007

Please don't use Select. Just use the method on the object, like Rows("1:1").AutoFilter. — Lance Roberts
The problem is the excel throwing the error for unknown reason... :( — Sunny D'Souza
I understand that I didn't point out the problem, which is why I left a comment instead of an answer. But you'll find it way easier to figure the errors out with cleaner code, like variant used in his answer. — Lance Roberts
yeah Lance, sorry am new to excel VBA. Actually i used the code Variant used below but still it throws the same error :(( — Sunny D'Souza

variant variant · Accepted Answer · 2011-08-19T04:51:02

Here's your code cleaned up:

Sub Macro34()

    ' Turn off autofiltering
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

    ' Turn it back on
    Rows(1).AutoFilter

    ' Set the autofiltering conditions
    Rows(1).AutoFilter Field:=6, _
        Criteria1:="=242", _
        Operator:=xlOr, _
        Criteria2:="=244"

    ' Copy only the relevant range
    Range("A1", _
          Cells(65536, Cells(1, 256).End(xlToLeft).Column).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy

    ' Paste the data into Sheet2 (assuming that it exists)
    Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False

End Sub

The key is that SpecialCells part.

Now, as much as I love a good autofilter copy/paste, when you're dealing with that much data, you might want to look into using ADO, which would allow you to query your Excel worksheet using SQL.

A good overview of ADO in VBA is provided here: http://www.xtremevbtalk.com/showthread.php?t=217783.

Cut Paste data from excel sheet using Autofilter in Excel VBA

2 Answers