Using Autofilter if criteria is not in list

nehpets12

Active Member
Joined
Feb 22, 2002
Messages
452
I want to check the Criteria in the filter list and if there is not a 1 then I dont want it to copy anything.

At the moment if there is not a 1 it copies whole sheet!!!




Code:
Sheets("Machine Information").Select
    Range("A65536").End(xlUp).Offset(0, 0).Range("A1").Select
    Myfilter = ActiveCell.Row
    Cells.Select
    
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:="1"
    Range("a2:C" & Myfilter).Select
    Selection.Copy
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try selecting only the visible cells:

Range("a2:C" & Myfilter).SpecialCells(xlCellTypeVisible).Select
 

Watch MrExcel Video

Forum statistics

Threads
1,118,111
Messages
5,570,257
Members
412,313
Latest member
pauloalex
Top