Sleeper: Ignore If statement if condition equates to error

[cejhoney]

I am running a macro which filters a column to show only records with "#N/A" (ie an error), and copies these to another worksheet.
As I am aiming for no errors, there will be occasions when there is no filtered data.

My problem is that, unless I use something like xlCellTypeVisible, when there is no data shown it still copies and pastes all the hidden records.
And if I do use it when no filtered data, I currently get an error "No cells were found"

I have tried many iterations of code correction and i think i'm currently blinded by staring at it so long!

My current if statement reads as follows:



Range("A1:V" & LastRow).AutoFilter Field:=Columns(CHtComments).Column, Criteria1:="#N/A"

If Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Count > 0 Then
Range("A2:V" & LastRow).Copy
Sheets("Comments").Select
irowComment = ActiveSheet.Cells(Rows.Count, BusinessUnit).End(xlUp).Row + 1 'works out last row with data in it then adds 1 to get first blank
Range("A" & irowComment).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range(Status & irowComment & ":" & Status & (ActiveSheet.Cells(Rows.Count, BusinessUnit).End(xlUp).Row)).Formula = "NEW"
Range(StatusDate & irowComment & ":" & StatusDate & (ActiveSheet.Cells(Rows.Count, BusinessUnit).End(xlUp).Row)).Formula = Now
End If


I think I might need to use some sort of On Error Resume Next statement, but I have never used these before.
When I tried adding that before the If statement, it just ignored the criteria and tried copying hidden rows - the exact opposite of what I want.
I'd like something that said, if this condition = error, skip over entire if statement and carry on with rest of the sub.

Please can someone offer some help?!

[Bob Phillips]

It behaves a bit oddly for me, but does this help


Set rng = Range("A2:V" & LastRow)
Range("A1").AutoFilter Field:=Columns(CHtComments).Column, Criteria1:="#N/A"

On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then

rng.SpecialCells(xlCellTypeVisible).Copy
Sheets("Comments").Select
irowComment = Sheets("Comments").Cells(Rows.Count, BusinessUnit).End(xlUp).Row + 1 'works out last row with data in it then adds 1 to get first blank
Range("A" & irowComment).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range(Status & irowComment & ":" & Status & (ActiveSheet.Cells(Rows.Count, BusinessUnit).End(xlUp).Row)).Formula = "NEW"
Range(StatusDate & irowComment & ":" & StatusDate & (ActiveSheet.Cells(Rows.Count, BusinessUnit).End(xlUp).Row)).Formula = Now
End If