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

 

[Clint123]

Unfortunately, new error message Run-time error "1004": Application defined or object defined error

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

 

[Norie]

Which worksheet do you want to filter?

Which range on that worksheet has the data you want to filter?

Which worksheet is the criteria range on?

Which worksheet is the copy to range on?

 

[Clint123]

Unfortunately, new error message Run-time error "1004": Application defined or object defined error

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

 

[Clint123]

Which worksheet do you want to filter? All of the data is in worksheet "EMEA - 12mth DEMD"

Which range on that worksheet has the data you want to filter? As above, we want to filter in the data in worksheet "EMEA - 12mth DEMD" and bring it into to show worksheet "Multiple SKU's"

Which worksheet is the criteria range on? "Multiple SKU's"

Which worksheet is the copy to range on?

"Multiple SKU's"

I hope that helps someone, "HELP ME" to solve this...

 

[Norie]

Sorry, all the ranges, including the data, are on the same sheet 'Multiple SKU's'.

Why does the code refer to Sheets("EMEA - 12mth DEMD")?

By the way, you haven't said which range of data you want to filter - filtering the entire worksheet might not be a good idea, and in fact could be the cause of the error.

 

[Clint123]

No sorry, perhaps I wasnt clear,

The data sits on Sheet ("EMEA - 12mth DEMD")

So from the Sheet ("Multiple SKU's') I go to advanced filter, "Copy to another location"

List range is taken from Sheet ("EMEA - 12mth DEMD")

Criteria range is taken from Sheet ("Multiple SKU's') as is Copy to.

Does that help?

 

[Norie]

Try this, but I still think you would be better to specify the range you want to filter.

    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

 

[Clint123]

Hi, unfortunately it still doesnt work...

Guess I cant send a file?

 

[Norie]

You could upload it to somewhere like Box.net and then post a link.

By the way, did you try changing from Cells to the actual range you want to filter?

For example, and it's not a good example, this with filters the userd range on the 'EMEA' sheet.

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

The reason that this is a bad example is because UsedRange can't always be relied on.

It would be better if the range to be filter was stated explicitly.s