1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

autofilter problem in excel

Discussion in 'Microsoft Office Specialist (MOS)' started by sirdragon, Jun 6, 2005.

  1. sirdragon

    sirdragon Nibble Poster

    70
    0
    11
    Hi guys,

    I am setting up an autofilter via a macro, so far it is working ok except.

    is there a way to check for a criteria when it may not be there.
    ie.
    1) filter for "N"
    2) copy the names column in different sheet
    3) turn autofilter off.

    can do all apart from the first as at times there may not be a "N" to find, so I would like to be able to cancel the autofilter if it doesnot find criteria1 "N"

    is this feasable or is it back to the drawing board.

    this is the code
    Code:
    'Find any night staff.
    	
    	Selection.AutoFilter Field:=3, Criteria1:="N"
    	'need to be able to cancel this instance of autofilter and go on to next field if no "N"
    	Range("namerge").Select
    	Selection.Copy
    	
    	Range("AJ28").Select
    	Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    		:=False, Transpose:=False
       
    	Selection.AutoFilter Field:=3
    	
    	Selection.AutoFilter Field:=4, Criteria1:="N"
    	Range("namerge").Select
    	Selection.Copy
    	
    	Range("Ak28").Select
    	Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    		:=False, Transpose:=False
       
    	Selection.AutoFilter Field:=4
    
    cheers

    martin
     
  2. sirdragon

    sirdragon Nibble Poster

    70
    0
    11
    many thanks for those that had a look....

    got it sorted now as I used a countif to check for the required "N" if it was >=1 ran the autofilter code.

    cheers martin
     

Share This Page

Loading...