The array when used in another workbook sheet to filter wont filter at all - - - Please advice!

[pedie]

Hi, I am using below code to filter my data...
but the array when used in another workbook sheet to filter wont filter at all - - - Please advice!

Thanks in advance for looking into this...

[/FONT]
[FONT=Courier New]Sub Test()
Dim wb As Workbook, MyFile As String
Dim MyArray() As Variant
MyArray = Sheet1.Range("A1:A3")
MyFile = ThisWorkbook.Path & "\Book2.xls"
Set wb = Workbooks.Open(Filename:=MyFile)[/FONT]
[FONT=Courier New]wb.Activate
With Sheets("AllDatas")
 .Range("A1:D20").AutoFilter Field:=3, Criteria1:=Application.Transpose(MyArray), Operator:=xlFilterValues
End With[/FONT]
[FONT=Courier New]MsgBox Sheets("AllDatas").Range("A1:A20").SpecialCells(xlCellTypeVisible).Count
Application.DisplayAlerts = False
wb.Close False
Application.DisplayAlerts = True
End Sub

 

[meldoc]

Hi Pedie

That works for me.

 

[pedie]

Hi Pedie

That works for me.

In sheet1 A1:A3
I have numbers
1,2 and 3 respectively....I dont know why it wont filter it....
it always return 0 count, I even trie debugging it...
non is selected in filter...

And yes i adapted this from the code you provided few days back

 

[meldoc]

Hi Pedie

Yes I see, I tried it out using text, and the Autofilter criteria has to be a string.
Using the Variant array, as a quick way populate the array is converting the range to type Double instead of String.
A quick workaround is to type into sheet1 A1:A3.

'1
'2
'3

IE, precede each number with an apostrophe, to force it to be text, if this isn't practical please advise and I will rewrite it for you.

 

[pedie]

Hi Pedie

Yes I see, I tried it out using text, and the Autofilter criteria has to be a string.
Using the Variant array, as a quick way populate the array is converting the range to type Double instead of String.
A quick workaround is to type into sheet1 A1:A3.

'1
'2
'3

IE, precede each number with an apostrophe, to force it to be text, if this isn't practical please advise and I will rewrite it for you.

Brian, it is working....with apostrophe
However this isnt so practicle...if there is another workaround maybe..
else i write to change it to be text and then proceed....


Thanks again.
Pedie

 

[meldoc]

Good morning Pedie.

Try this.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> Test()<br><SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook, MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  lr = Cells(Rows.Count, "A").End(xlUp).Row<br>  <SPAN style="color:#00007F">ReDim</SPAN> MyArray(1 <SPAN style="color:#00007F">To</SPAN> lr) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>      <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> lr<br>        MyArray(i) = Cells(i, "A")<br>      <SPAN style="color:#00007F">Next</SPAN> i<br>MyFile = ThisWorkbook.Path & "\Book2.xls"<br><SPAN style="color:#00007F">Set</SPAN> wb = Workbooks.Open(Filename:=MyFile)<br>wb.Activate<br><SPAN style="color:#00007F">With</SPAN> Sheets("AllDatas")<br> .Range("A1").AutoFilter Field:=3, Criteria1:=MyArray, Operator:=xlFilterValues<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>MsgBox Sheets("AllDatas").Range("A1:A20").SpecialCells(xlCellTypeVisible).Count<br>Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>wb.Close <SPAN style="color:#00007F">False</SPAN><br>Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[pedie]

Brian, cool! now it works perfect...
so we can use array as string, integer,variant etc?
looking at this line...this leads to another question...
"ReDim MyArray(1 To lr) As String"
I thought we can only use as "Dim myarray() as variant

Good morning Pedie.

Try this.

Option Explicit
Sub Test()
Dim wb As Workbook, MyFile As String
Dim i As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
ReDim MyArray(1 To lr) As String
For i = 1 To lr
MyArray(i) = Cells(i, "A")
Next i
MyFile = ThisWorkbook.Path & "\Book2.xls"
Set wb = Workbooks.Open(Filename:=MyFile)
wb.Activate
With Sheets("AllDatas")
.Range("A1").AutoFilter Field:=3, Criteria1:=MyArray, Operator:=xlFilterValues
End With
MsgBox Sheets("AllDatas").Range("A1:A20").SpecialCells(xlCellTypeVisible).Count
Application.DisplayAlerts = False
wb.Close False
Application.DisplayAlerts = True
End Sub

 

[meldoc]

Hi Pedie

Sorry for the late reply, I had to go out shopping.

I thought we can only use as "Dim myarray() as variant

No declaring Arrays the standard variable types depending on there use, is quite normal, however the Array has to be populated by some type of loop, one element at a time.

The advantage of using a Variant Array, is that you can populate it very quickly without looping, using something like.

MyArray = Sheet1.Range("A1:A10000")

This works great most of the time, but obviously not this time.

 

[pedie]

Brian, thanks again for cofirming that...moreoever i have seen few videos on the link you suggested about arrays and others too..


Thanks again...