VBA - Autofilter and copy visiable data to another feild

Question

I have a dataset where I want to the VBA to do an auto-filter and in column B just unselect 0 and keep all the other values.

Then copy the visible cells to a new sheet. Can someone help me what the error is

Thanks

Sub findlastrowwithvaluefilter()
ActiveWorkbook.Sheets("CASCADE -Offshore Upload Format").Activate

LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

    With Range("A1:Q" & LastRow)
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="Select All", Operator:=xlAnd, Criteria1:="<>0"

        End With

    Range("A2:Q" & LastRow).Select
    ActiveSheet.Range("A1:Q1" & LastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

MsgBox (LastRow)

End Sub

Do you get a runtime error? If so, what does it say? Does it give you the choice to `Debug or Continue`? If `Debug` what line does it show the error? If you can't debug, add a breakpoint to the first line and step through with F8 until you get an error. What line is that on? Please [edit these details into your question](http://stackoverflow.com/posts/30761460/edit). If it's not an actual error, can you tell us what it is doing/not doing vs. what is expected? — Byron Wall, Jun 10 '15 at 16:53
There is no error, what is happening is that the filter dosent select everything except 0. So Either it will select all when I remove the criteria, or will not select anything in the check-box — Brian, Jun 10 '15 at 18:06
Should the second one be `Criteria2` instead of `Criteria1`? If that doesn't do it, I'll take a look shortly. Also does this work outside of VBA? If you manually set the filters? — Byron Wall, Jun 10 '15 at 18:08
Yes it works manually, I am trying to automate the process. So what I am trying to do is if in column B if they are zero I want to filter them out so only values are there, and then copy A to Q and get the last row from the macro . — Brian, Jun 10 '15 at 18:14

score 1 · Accepted Answer · answered Jun 10 '15 at 16:54

1

Replace

.AutoFilter Field:=2, Criteria1:="Select All", Operator:=xlAnd, Criteria1:="<>0"

with

.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd

and should work

answered Jun 10 '15 at 16:54

TylerDurden

135
1
9

It the same issue as before, the filter will not select anything, so nothing is copied. – Brian Jun 10 '15 at 18:05
1

This should be the solution. So something may be off up above. What value is being returned for `LastValue`? Using `SpecialCells` is not the best approach for this. See [this discussion for alternatives](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). Note that `xlCellTypeLastCell` will ignore hidden rows so you want to clear any existing filter if you are coming in with one. Inside your `With` add a `Debug.Print .Address` or `.Select` to see what range you are dealing with. – Byron Wall Jun 10 '15 at 18:21

score 0 · Answer 2 · answered Jun 11 '15 at 14:20

it was a very small syntax issue. below is the working code.

The way to write the criteria so that zero is filtered out is

Criteria1:="<>0". the = sign should be outside the parenthesis.

    Sub findlastrowwithvaluefilter()
ActiveWorkbook.Sheets("CASCADE -Offshore Upload Format").Activate

LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row


    With Range("A1:Q" & LastRow)
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd
        End With

    Range("A2:Q" & LastRow).Select
    ActiveSheet.Range("A1:Q1" & LastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

MsgBox (LastRow)

End Sub

VBA - Autofilter and copy visiable data to another feild

2 Answers2