GB Allan


Hi,

I wrote the following code, which filters data by #N/A in one sheet, and then copies those records to another sheet in the same workbook:

Sheets("NewData").Select
Cells.Select
Selection.AutoFilter Field:=5, Criteria1:="#N/A", Operator:=xlAnd
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("DATA").Select
Range("A4").End(xlDown).Select
ActiveCell.Offset(1, 0).Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

The problem is, if there are no #N/A records, it copies the field headings over. (Also, if there are no records, A2 is not visible). I want it to check to see first if there are any #N/A records in field 5, if so, I don't want it to do the copy at all.

Thank you,

GB




Re: If Test for Autofilter

Peter Mo.


Hi

There are several ways of doing this, but one of the easiest might be to do a "find" on column 5 e.g.

Dim rng as Range

set rng = columns(5).Find(What:="#N/A")

If Not rng is Nothing Then

' we have found an example so we can do the filter

Regards

Peter Mo.






Re: If Test for Autofilter

GB Allan

Thank you - that was exactly what I was looking for - and it appears to be very applicable to other situations. Thanks.