Column Paste Special Value - Array

[mrsushi]

Hi,

I'm a bit inexperience in using arrays, but the paste special value runs a little longer than i would like.
Column A can have 200,000 - 300,000 items .

Is there a way to speedup the below code using Array or an alternative way making the code work faster??

Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Many thanks
M

 

[Joe4]

Are you just trying to convert all of the formulas in column A to values?
If so, try this:

Dim rng as Range
Set rng=Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.Value = rng.Value

 

[mrsushi]

Are you just trying to convert all of the formulas in column A to values?
If so, try this:

Dim rng as Range
Set rng=Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.Value = rng.Value

Thank you very much. Worked a treat.

In addition to this code, I have noticed when you apply the conversion, it remioves preceeding zeros. for example 0025605 becomes 25605. Is there any way to keep the preceeding zero's? pls

Many thanks
M

 

[Joe4]

I am guessing that the formula is returning a text value, and those zeroes you see really are just the result of Custom Formatting and not really there.
Before your run the code, what is the formula in the cell, and what is the cell formatting?

 

[mrsushi]

I am guessing that the formula is returning a text value, and those zeroes you see really are just the result of Custom Formatting and not really there.
Before your run the code, what is the formula in the cell, and what is the cell formatting?

The formula in A is = right(M2, 7). Please see image.

 

[Joe4]

Hmmm...

I see what you mean.
See if it is any faster if we can make these updates to your original code:

Dim rng As Range

Application.ScreenUpdating = False

Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Application.ScreenUpdating = True

 

[mrsushi]

Hmmm...

I see what you mean.
See if it is any faster if we can make these updates to your original code:

Dim rng As Range

Application.ScreenUpdating = False

Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Application.ScreenUpdating = True

Hi, I tried that, but the column needs to be converted to a number and preceding zeros to be kept. Any ideas?

 

[Joe4]

Hi, I tried that, but the column needs to be converted to a number and preceding zeros to be kept. Any ideas?

That is actually not possible.

Leading zeroes have no meaning to the integer value of numbers in Excel, and Excel will automatically drop them.
This is easy to see. Format a column as Number, then try entering a number like 0123 and watch what happens (Excel changes it to 123).

If you want to maintain leading zeroes, then you must format the cells as Text first. However, that makes your entries Text and not Numbers.

What you can do is keep them as numbers, and just apply Custom number formatting to them. This will display leading zeroes in front, but they aren't really there in the actual value (if you select the cell and look at its value in the formula bar, you can see that).

If this last option will work for you, then let's go back to our original code and just add Custom Formatting at the end, i.e.

Dim rng As Range
Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.Value = rng.Value
rng.NumberFormat = "0000"