Advanced Filter

[elgringo56]

When I do an Advanced Filter on a list with a copy to another location, the formats(color, font, etc.) of the cells which were filtered are also copied along with the data. Is there a way to prevent this as I only want the data.

 

[j844929]

When you paste the data, just have excel paste the values....

 

[elgringo56]

The problem with that, j844929, is that an advanced filter paste the data for you, you are not given a choice, not that I can see, anyway.

 

[j844929]

What are you doing to copy the data to another location? Is it part of a vb script? If so, what is it?

 

[elgringo56]

If you go to "Data", Advanced Filter, you will see that part of the criteria is to "copy to a different location" after filtering. This is a selectable option in the Advanced Filter. And, yes, I will be doing it in a macro, therefore, would like to figgure out if its possible to do it without the formats of the cells copying.

 

[ctrlaltdel]

elgringo56,

As far as I know when you use data/filter/advanced filter, and copy to another location, you have no choice but for the formats to come along. Perhaps someone could provide a piece of code to deal with this, but I always just select the copy to range after filtering and go to edit/clear/formats.

Good Luck.

 

[elgringo56]

Yes, when doing the Filter manualy, I would do that, however, when doing from within a macro, in order to get rid of the formats, I have to have a macro to reformat the area copied to, which is a pain.

 

[ctrlaltdel]

Yes, when doing the Filter manualy, I would do that, however, when doing from within a macro, in order to get rid of the formats, I have to have a macro to reformat the area copied to, which is a pain.

Wouldn't these two lines placed in your code just after the advanced filter work?

Range("C1:C100").Select
Selection.ClearFormats

You only need to replace "C1:C100" with the range where you copied the data. It actually seems somewhat painless.

 

[elgringo56]

Yes, that would do as a workaround, however, the question was, "is there a way to prevent the formats from copying?" I dont really like "work arounds" if it can be helped as sometime down the road, somone may have to debug something and they have to try and figgure out why a workaroud was used. In that respect, they are a pain.

 

[ctrlaltdel]

Understood.

Can you provide an example of the code you are using for the filter? I think that the only possibility will be another workaround (given your definition), but you never know.