VBA autofilter returns blank cells then jump code

[Blunder1]

Hi,

In my vba i need to copy data. I do this using the auto filters. However, when it returns no data i need it to jump the next bit of code by ising an IF statement.

I have tried a few methods, the below being the last but cannot get it recognise no data returned ( i have names sheets 'we' and 'wf' ):-

we.UsedRange.AutoFilter field:=4, Criteria1:="NOK"
Set rng = we.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then
we.Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=wf.Range("a65536").End(xlUp).Offset(1, 0)
End If

Thanks in advance

Blunder

 

[G2K]

.offset select the range next to active cell hence it's not working

you need to set a range first, then copy filtered range. Ex -

 Set Rng = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
Rng.SpecialCells(xlCellTypeVisible).Copy ShTarget.Range("A1")

Hope it helps

 

[Blunder1]

thank you G2K. It didn't work ooutright, but pointed me in teh right direction.

Thank you again

Blunder