Home How to filter a VBA array on Error 2042
Reply: 1

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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.299134 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO