Home How to filter a VBA array on Error 2042

# How to filter a VBA array on Error 2042

P.Vee
1#
P.Vee Published in 2018-01-12 17:32:43Z
 I have a range of values in Excel that I want to filter on #N/A. Since my range is of large size, I want to create an array in VBA and filter on #N/A (or Error 2042). For example, with the following data Amy 25 Louise 30 Carl 40 Pete #N/A Jeff 32 Alyson 21 Valerie #N/A  I want to be able, when running my code, to have the following output Pete #N/A Valerie #N/A  Keep in mind that I have a lot of entries, thus do not want to use filter in Excel.
Gary's Student
2#
Gary's Student Reply to 2018-01-12 18:07:49Z
 I agree with Davesexcel. AutoFilter is very fast, even on large datasets. Starting with this on Sheet1: This tiny macro: Sub Macro1() Columns("A:B").AutoFilter Range("A:B").AutoFilter Field:=2, Criteria1:="#N/A" ActiveSheet.AutoFilter.Range.Copy Sheets("Sheet3").Paste ActiveSheet.ShowAllData End Sub  Adapted from the Recorder, will produce this on Sheet3: The key issue is that you do not have to loop through the rows one-by-one.
 You need to login account before you can post.
Processed in 0.299134 second(s) , Gzip On .