VBA Advanced Filter

[Dimitriy]

Hey Everybody,

I am having a problem with the following code:

With Sheets("Standard Menu Individual Items")
Set Rng = Range("D8:F17")
End With
With Rng
.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"B21:B22"), CopyToRange:=Range("D25:F43"), Unique:=True
End With

When I wrote the code in a smaller environment outside of my main spreadsheet, it worked fine. But when I added this piece of code to the rest of my code it gives me the following error message:

"Run-time error 1004 - Connot change part of a merged cell"

Any suggestions? I am really stuck...:banghead:

[p45cal]

not want you wanted to hear, but don't mix merged cells and VBA - a nightmare.

[Dimitriy]

Well, I am not sure which cells VBA is referring to. My main page has several merged cells, so I not sure how to resolve this. Do you think it's having a problem with the cells that fall within the same columns that the code is trying to use? I am just not that familiar with this error message, so that makes it hard to fix the problem. Let me know if you have any suggestions.

[p45cal]

Well, it's likely to be either a merged cell or two in D8:F17 and/or merged cells in D25:F43. You could select one of them, bring up the Format Cells dialogue box and uncheck the merge cells checkbox, then see if the macro complains. If it still does, do the same with the other range.

[mdmackillop]

You can ofter achieve your layout using Centre Across Selection, rather than Merged Cells. If you can, get rid of all merged cells.