INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)

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

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

hi,

Put this formula in a cell on some other sheet, assuming that your table in in Sheet1...

=SUBTOTAL(3,Sheet1!V:V)-1

...and name that cell VisibleRows.

Then your code...

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 [VisibleRows] > 0 Then
            Intersect(Range(.Cells(2, 1), .Cells(.Rows.Count, 1)).EntireRow, .EntireColumn).Select
        End If
    End With
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close