Column Paste Special Value - Array

mrsushi

Board Regular
Joined
Nov 18, 2006
Messages
110
Office Version
  1. 2010
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,635
Office Version
  1. 365
Platform
  1. Windows
Are you just trying to convert all of the formulas in column A to values?
If so, try this:
VBA Code:
Dim rng as Range
Set rng=Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.Value = rng.Value
 

mrsushi

Board Regular
Joined
Nov 18, 2006
Messages
110
Office Version
  1. 2010
Are you just trying to convert all of the formulas in column A to values?
If so, try this:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,635
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 18, 2006
Messages
110
Office Version
  1. 2010

ADVERTISEMENT

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.
 

Attachments

  • picture.png
    picture.png
    13.1 KB · Views: 1

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,635
Office Version
  1. 365
Platform
  1. Windows
Hmmm...

I see what you mean.
See if it is any faster if we can make these updates to your original code:
VBA 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

Board Regular
Joined
Nov 18, 2006
Messages
110
Office Version
  1. 2010
Hmmm...

I see what you mean.
See if it is any faster if we can make these updates to your original code:
VBA 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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,635
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
Dim rng As Range
Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
rng.Value = rng.Value
rng.NumberFormat = "0000"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,494
Messages
5,625,083
Members
416,070
Latest member
Austen G

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top