Is it possible to paste sorted unique values?

[MPW]

Hi All,

Using XP with Office 2003

Currently I am copying 3 columns to a new location(one column is not next to the other two).
Next I use an AdvancedFilter to copytorange a unique list to a 2nd sheet. Last of all the list is sorted.

The clean up involves clearing the 1st 3 columns that I used for the unique list and also reformatting the list that was created since it brought the original formatting with it.

The process works but it takes too long. I would like to combine the processes.

Is it possible to do this all in one step?
Is it possible to do the advancedfilter copy with just values so I don't have to reformat the cells?
Is it possible to do the advancedfilter copy from columns that are not next to each other?

Any Ideas for some or all of the answers?

MPW

 

[Norie]

MPW

Are you using code for this?

If you are please post it.

 

[MPW]

Here is part of the code

Sheets("Script").Range("E56:F5692").Copy

Sheets("Script").Range("AA56").PasteSpecial Paste:=xlPasteValues, _
operation:=xlNone, skipblanks:=False, Transpose:=False

Sheets("Script").Range("J56:J5692").Copy

Sheets("Script").Range("AC56").PasteSpecial Paste:=xlPasteValues, _
operation:=xlNone, skipblanks:=False, Transpose:=False
    
Sheets("Script").Range("AA56:AC5692").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Sheets("Stats").Range("R197"), _
Unique:=True

Sheets("Script").Range("AA56:AC5692").Clear

Sheets("Stats").Range("AF197:AF1080").Copy

Sheets("Stats").Range("B197:AE1080").PasteSpecial _
Paste:=xlPasteFormats, operation:=xlNone, skipblanks:=False, _
Transpose:=False

Sheets("Stats").Range("AF199").Copy

Sheets("Stats").Range("B199:AE1080").PasteSpecial _
Paste:=xlPasteFormats, operation:=xlNone, skipblanks:=False, _
Transpose:=False
    

Sheets("Stats").Range("R197:R1000").Sort Key1:=Range("R197"), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Some of these ranges are actually variables that are created on the fly but for clarity I put in actual range references.

 

[MPW]

FYI
I tied to Copy over all the columns at once and the delete the columns that I didn't need. It actually doubled the amount of running time!
Back to the Drawing Board.

 

[MPW]

I have found that unhiding the hidden rows does speed up the process but it is still too slow to be acceptable to most end users. If it is not possible to combine these functions does anyone have an alternate method?