How do I create an 'if then' statement on a filter

[andy7172289]

The code below is an extract of what I've created so far and works. The data being filtered is basically 1, or 0 mostly with a few other values. The only thing I need is for the column to be filtered on '0's for me to them copy and paste the data to another tab in the workbook. It needs to say = if this filter on this column has no '0's then move on to the next step (which would be checking the nect column filter along), but if a '0' is present then filter on the '0' and follow these steps instead (which would involve filtering on '0' and copying data to another tab).

Can anyone help show me how to write that code?

If any clarification is needed please ask.



Range("V3:AS3").Select
Selection.AutoFilter
Range("V4").Select
ActiveSheet.Range("$V$3:$AS$232387").AutoFilter Field:=1, Criteria1:="0"
Range("C784:F1999").Select
Range(Selection, Selection.End(xlDown)).Select

 

[SkipVought]

hi,

Put this formula in a cell on some other sheet, assuming that your table in in Sheet1...
[tt]
=SUBTOTAL(3,Sheet1!V:V)-1
[/tt]
...and name that cell VisibleRows.

Then your code...

Sub filtr()
'Range("V3:AS3").Select
'Selection.AutoFilter
'Range("V4").Select
'ActiveSheet.Range("$V$3:$AS$232387").AutoFilter Field:=1, Criteria1:="0"
'Range("C784:F1999").Select
'Range(Selection, Selection.End(xlDown)).Select
    With Range("V3").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="0"
        If [[b]VisibleRows[/b]] > 0 Then
            Intersect(Range(.Cells(2, 1), .Cells(.Rows.Count, 1)).EntireRow, .EntireColumn).Select
        End If
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]