Excel Advanced Filter - Macro - issue with finding the last cell

[Clint123]

Hi All,

First time using this forum so welcome all.

Just a question with my "Excel Advanced Filter - Macro" as below. Everything works fine

Issue: is the Range and that it changes "A4" will always be the first cell, however it may go from "A4:A6" or "A4:A333". Therefore, I need the Macro to take the last field etc.

MultipleSKU Macro
'
Sheets("EMEA - 12mth DEMD").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A4:A12"), CopyToRange:=Range("F2:U2"), Unique:= _
False

End Sub

Thanks if anyone can help

 

[Norie]

Which range are you referring to?

Is it the criteria range?

If it is which worksheet is it on?

 

[Clint123]

Yes, its the Criteria range which changes.

It is on worksheet "Multiple SKU's"

As mentioned however that the Macro currently works fine, just doesnt re-calculate then the range changes...

 

[Norie]

Perhaps.

With ("Multiple SKU's")
     Sheets("EMEA - 12mth DEMD").Cells.AdvancedFilter Action:=xlFilterCopy, _
          CriteriaRange:=.Range("A4" & .Range("A" & Rows.Count).End(xlUp)), CopyToRange:=Range("F2:U2"), Unique:= False
End With

By the way, are you really filtering the entire 'EMEA - 12mth DEMD' sheet?

 

[Clint123]

Nope: still getting blocked at Range as I have highlighted below...

heets("EMEA - 12mth DEMD").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("A4" & .Range("A" & Rows.Count).End(xlUp)), CopyToRange:=Range("F2:U2"), Unique:=False

By the way, are you really filtering the entire 'EMEA - 12mth DEMD' sheet? I am filtering all the records within (so approx. 17 Accross and 10,000 down).

 

[Norie]

What do you mean 'blocked'?

If you are only filtering that range why not limit the filter to that range?

 

[Clint123]

When I say "Blocked" I mean I get the message: Compile Error: Invalid or unqualified reference

 

[Norie]

Did you use the code I posted, including the With part?

 

[Clint123]

Yep, below is what the VBA string now looks like. Still doesnt work unfortunately...New error "Compile Error: With object must be user-defined type, Object, or Variant.

Sub MultipleSKU()
'
' MultipleSKU Macro
'


'
With ("Multiple SKU's")
Sheets("EMEA - 12mth DEMD").Cells.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("A4" & .Range("A" & Rows.Count).End(xlUp)), CopyToRange:=Range("F2:U2"), Unique:=False
End With


End Sub

Thoughts???

 

[Norie]

Oops, forgot Worksheets which is kind of important.

Sub MultipleSKU()
 '
 ' MultipleSKU Macro
 '


 '
 With Worksheets("Multiple SKU's")
    Sheets("EMEA - 12mth DEMD").Cells.AdvancedFilter Action:=xlFilterCopy, _
             CriteriaRange:=.Range("A4" & .Range("A" & Rows.Count).End(xlUp)), CopyToRange:=Range("F2:U2"), Unique:=False
 End With


 End Sub