Copy n Paste non-contigous columns 1

[Mantle51]

Hello,
The following line copies 3200+ lines of non-contiguous columns and pastes together at A1. Problems are two-fold: It takes an enormously long time (i.e. 30-40 seconds) for this to complete and it does not paste in order of columns identified, rather it pastes in the order in which they appear on spreadsheet. This forces me to do an additional cut n paste. Is there a better method for copying non-contiguous columns?
THanks.

[AI:AI,AM:AM,BW:BW,M:M,R:R].Copy Destination:=Range("A1")
''''I'd like them to appear in this order

 

[AnotherHiggins]

Copy the entire sheet, then delete unwanted columns. That should only take a couple of seconds to run.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PHV]

That should only take a couple of seconds to run
But don't solve the columns order issue ...

 

[DaleWatson123321]

Excel's "Advanced Filter" is a powerful component that is often overlooked. One of its many "powers" is its ability to extract data

- whatever data you require... by way of the criteria you specify.

[highlight]- AND you can easily pick WHATEVER COLUMNS you want,[/highlight]

[highlight]- AND you can place the columns in WHATEVER ORDER you perfer - just by the placement of the field names in your extraction range.[/highlight]

The following is an example of code to use.

Sub Extract_Records()
    Range("data").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:="crit", _
    CopyToRange:=Range("extrange"), _
    Unique:=False
End Sub

The names is quotes refer to range names: one for the data range, one for the criteria, and one for the extraction range. You can of course use whatever range names you prefer.

A couple of pointers...

The top row of you "data" range needs to be the one with your field names. There HAS to be a unique field name for all the columns in your data range. The data range can extend beyond the last row of data - thus allowing for additional data being added at a later time.

The extraction range should be on a SEPARATE sheet, as it's MUCH easier and cleaner. The top row of the extraction range needs to include field names from the data range. But as mentioned, you can include ONLY those names you prefer, AND you can change the ORDER.

It also MUCH better to place your criteria on a SEPARATE sheet. The criteria needs to be at least two cells, the top cell to contain a field name, and the bottom cell to contain the criteria - and this instructs Excel on what data to extract. If in this particular case, you want ALL records extracted, then simply leave the bottom cell BLANK.

If there is a need to use a formula, then it's REQUIRED that you do NOT use a field name in the top row of the criteria (above the formula). Formulas can be as extensive as is required in order to isolate precicely the type of records you want to extract. With one formula, you can reference multiple fields. Or, you could use multiple fields (entered as labels in adjacent cells - i.e. the top row of your criteria range). For anyone wanting to explore the real power of the Advanced Filter, you'll become excited and surprised at just how powerful it can be - by trying different formulas. Besides the option to create an "AND" condition by referencing multiple fields, you can also create an "OR" condition by using multiple rows in your criteria range. And you can have compound conditions in one or more formulas in your criteria. Just remember, where you use a formula, Excel requires that you do NOT use a field name above that formula. Also expect to see "#NAME?" in the cell where you use a formula for your criteria. This is normal in this situation.

And by the way, once set up, this method of extraction is "[highlight]as fast as it gets/highlight]".

On last point... The very same criteria used for extraction can also be used for "DATABASE formulas" - i.e. DSUM, DCOUNTA, DAVG, etc. These database formulas consists of 3 arguments - from left-to-right...

a) reference to the data range "data" for example (or whatever you've named your data range,

b) what is sometimes called the "offset", which refers to the field you want to reference in your data range - for example where you want to SUM the values in a particular field. This can be by number (i.e. 5 would be the fifth column in the range you are using) or you can reference the field name by using double quotes around the field name, and

c) the criteria range.

By using formulas that reference date requirements, one can create various matrices - for example showing the type of products sold by different salespersons by week, month, year, etc.

While Pivot Tables are an option, there are some data manipulation options possible with the Advanced Filter that are not possible with Pivot Tables. For example, after proper setup, it's possible to place your cursor on any value in a matrix, and at the click-of-a-button, extract the "data behind that value". So it becomes very easy to examine (and report on) the records that are "driving" the totals in a summary.

That's my 2 cents worth on the Advanced Filter . I hope this at least serves to allow you to extract the data (columns) you want, in the order you want.

Regards, Dale Watson

 

[Gavona]

Just to add to Dale's suggestion: In your case you appear to want all the records so you simply omit the CriteriaRange.

Personally I would go with Dale's solution "[highlight]as fast as it gets[/highlight]".

However if your columns contain formulae then advanced filter will not preserve them. In this case have a look at Data,Sort,Options Sort left to right. You would of course need a helper row specifying the order of the columns.


Gavin

 

[Mantle51]

Thanks so much for the informative response, Mr. Watson.