vba advancedfilter, flexible criteria

[Ghostbuster_one]

hi all
i am wondering how i can make the criteria in my advance filter flexible. I now have data added up to row D5, but it might as well be only row D4 or D1 and D2.
How can i get it to read lines that are populated ?
Many thanks!
Ralph

Sub ADV()
'
' ADV Macro
'

'i am in file 2

Cells.Advancedfilter Action:=xlFilterInPlace, CriteriaRange:=Workbooks( _
"File 1.xlsm"). _
Sheets("List of Acc. - Resp.").Range("A1:AD5"), Unique:=False
Windows( _
"File1.xlsm"). _
Activate
End Sub

 

[Ghostbuster_one]

and to add, the criteria are in a file ( File1) which was downloaded earlier and is open in the backside.
The name however can be different, so how do i get the criteria to look into that specific file?
Or should i build something in autofilter , with specifying multiple criteria?
Selection.AutoFilter Field:=3, Criteria1:=Entity, Operator:=xlAnd

many thanks !
RAlph

 

[RoryA]

You could use Workbooks("File 1.xlsm").Sheets("List of Acc. - Resp.").Range("A1").Currentregion rather than a fixed address.

 

[Ghostbuster_one]

HI Rory
this worked very well, thank you !
Maybe you are able to answer the other question as well?
The below sub is based on a file i opened manually, but in real time the criteria are on a file downloaded in another sub, which changes names...
How do i get it to recognize the file that i downloaded previously? the code for that is"
ChDir Trial_Balance_Path
Workbooks.Open Filename:=Trial_Balance_Path_File




Sub Advancedfilter()
'
' Advancedfilter Macro
'

'Select all rows in the sheet
Rows("1:1").Select
'remove top line
Selection.Delete Shift:=xlUp
'select all the cells of the current workbook
Cells.Select
'advanced filter on above mentioned all cells and criteria on other file
Cells.Advancedfilter Action:=xlFilterInPlace, CriteriaRange:=Workbooks( _
"BE1182 InterCompany Daily reconciliation R Cloud V6.xlsm").Sheets("Selected_Accounts"). _
Range("C1").CurrentRegion, Unique:=False
End Sub


Thanks sooo much for you help

 

[RoryA]

Store the workbook object in a variable that is visible to both routines when you open it (or, preferably, pass it as an argument to that filter routine).

 

[Ghostbuster_one]

Store the workbook object in a variable that is visible to both routines when you open it (or, preferably, pass it as an argument to that filter routine).

Hi Rory, the criteria actually sits in a tab of the file in which the VBA is recorded. Isn't there an easy way, like this.workbook? I have no clue ....
Thanks

 

[Ghostbuster_one]

Hi Rory, the criteria actually sits in a tab of the file in which the VBA is recorded. Isn't there an easy way, like this.workbook? I have no clue ....
Thanks

HI Rory
if i add this to the VBA

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Selected_Accounts")

how would it fit into the advanced filter i placed on another file than the above?

Cells.Advancedfilter Action:=xlFilterInPlace, CriteriaRange:= ? . Range("C1").CurrentRegion, Unique:=False ?

Really its keeping me busy all day

Thx

 

[RoryA]

Replace the question mark with ws - provided you have declared the variable in scope for both procedures, not just the first one.

 

[Ghostbuster_one]

aah loosing it
declared file one, when opening it:
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Selected_Accounts")


and declared file 2 when opening it

Dim wb2 As Workbook: Set wb2 = ThisWorkbook
Dim ws2 As Worksheet

wb1.Cells.Advancedfilter Action:=xlFilterInPlace, CriteriaRange:=ws.Range("C1").CurrentRegion, Unique:=False


how come it doesnt recognize that the advanced filter should be run from the first and the criteria from the second...?
I have lost a few hairs today

 

[RoryA]

The scope of your variables is wrong. A little light reading: Understanding Scope