Quick macro help - paste values

[theta]

Hi

I need to prevent numbers stored at text. At present I use

oWS.UsedRange.Values = oWS.UsedRange.Values

This was working fine but now runs out of memory as dealing with a huge range

Can this be changed so that it does the .Values = .Values for each column in the Usedrange...scrolling until the operation has completed for all columns. (For each Column in UsedRange - or use a counter with UsedRange.Columns.Count ?)

This should then fix the memory problem

(or maybe rng as Range - for each rng ?)

Any help appreciated

Kind regards

 

[theta]

This is the row that gets highlighted

.Rows(1).AutoFilter

I can't see how our small change to the code has caused this much of an effect

Do I need to reset the UsedRange after the .Value method using Cols?

 

[theta]

Noticed that A:AD where the headers are form named range 'Table1'. If I select A:AE (1 extra column) - the autofilter option greys out?

Doesn't happen when just using UsedRange.Value = oWS.UsedRange.Value

?

 

[Andrew Poulsom]

You still have said what the text of the error is.

 

[theta]

I said before : Error: 1004 AutoFilter method of Range class failed

Just had a look at the two outputs using the two methods

Using the fix - XML Data is clearly still a table (will not autofilter when the entire Rows(1) selected)

Original method - Plain data (no table - no named range table) - works when Rows(1) selected

Why are the two codes producing such different results? It was only split by columns...

 

[Andrew Poulsom]

Sorry, I don't know.

 

[theta]

It's just confusing. Logically they appear to do the same thing in 2 different ways.

But UsedRange.Value = oWS.UsedRange.Value removes and table references

Any thoughts anybody else?

Thanks for all your help so far, been scratching my head for a fair while!?