Filter Array Non Empty

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Filter Array Non Empty

Post by adeel1 »

Hi All

why below line isn't filtering array blanks

Code: Select all

v = Filter(Application.Transpose(Evaluate("IF(--LEFT(A2:A" & p & ",3)=" & c & ",A2:A" & p & ","""")")), "", False)
means yellow highlighted should filtered
Capture.PNG
Adeel
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter Array Non Empty

Post by HansV »

I get a Type Mismatch error when I try to run the macro...
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Filter Array Non Empty

Post by adeel1 »

Thnx for your reply, its showing error because variable v is empty , you remove filter function its works with spaces

Code: Select all

v = Application.Transpose(Evaluate("IF(--LEFT(A2:A" & p & ",3)=" & c & ",A2:A" & p & ","""")"))
filter function isn't working in line eventually

Capture.PNG
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter Array Non Empty

Post by HansV »

You cannot filter for "" since EVERY string contains the empty string.
I'd place the code in a standard module and change it to

Code: Select all

Sub nnn()
    Dim p As Long
    Dim s
    Dim n As Long
    Dim c
    Dim v
    Dim k As Long
    ActiveSheet.UsedRange.Offset(, 1).Clear
    p = Cells(Rows.Count, 1).End(xlUp).Row
    s = Evaluate("=UNIQUE(LEFT(A2:A" & p & ",3))")
    n = 3
    For Each c In s
        v = Filter(Application.Transpose(Evaluate("IF(--LEFT(A2:A" & p & ",3)=" & c & ",A2:A" & p & ",""x"")")), "x", False)
        k = k + 1
        Cells(2, n + k).Resize(UBound(v) + 1, 1).Value = Application.Transpose(v)
    Next c
End Sub
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Filter Array Non Empty

Post by adeel1 »

thnx , its worked :clapping: but there is another issue occur i am unbale to diagnosed yet, why this is happening

if you run this line (for make data)

Code: Select all

Cells(2, 1).Resize(500000).Value = "65780"
this data will appear in Col A then run above code all this data should come but only 41248 rows are coming, can you check please

Adeel

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter Array Non Empty

Post by HansV »

That is a limitation of Transpose: it can handle up to 65536 items.
500000 Mod 65536 = 41248.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Filter Array Non Empty

Post by adeel1 »

Thnx, means this approach will work if data is under 65k rows :sad:

thnx lot for your help, :thankyou:

Adeel

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: Filter Array Non Empty

Post by p45cal »

Power Query does a good job of this.
You do not have the required permissions to view the files attached to this post.

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Filter Array Non Empty

Post by adeel1 »

Thank you p45cal for your time and effort :clapping: :clapping:

Adeel