I have a sheet that has about 40,000 rows and each row contains a formula in
column "W" (23). I need to convert the formula to values. My current code
follows:

ActiveSheet.UsedRange.Columns(23).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Although the above works, I'm sure there is a more efficient method that
could be written in a single line of code. Can anyone help me out?

Thanks much in advance.

Use something like

With ActiveSheet.UsedRange.Columns(23)
.Value = .Value
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"quartz" wrote in message
...
I have a sheet that has about 40,000 rows and each row contains
a formula in
column "W" (23). I need to convert the formula to values. My
current code
follows:

ActiveSheet.UsedRange.Columns(23).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Although the above works, I'm sure there is a more efficient
method that
could be written in a single line of code. Can anyone help me
out?

Thanks much in advance.

Hi
with
ActiveSheet.UsedRange.Columns(23)
.value=.value
end with

"quartz" wrote:

I have a sheet that has about 40,000 rows and each row contains a formula in
column "W" (23). I need to convert the formula to values. My current code
follows:

ActiveSheet.UsedRange.Columns(23).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Although the above works, I'm sure there is a more efficient method that
could be written in a single line of code. Can anyone help me out?

Thanks much in advance.

Thanks.

"Chip Pearson" wrote:

Use something like

With ActiveSheet.UsedRange.Columns(23)
.Value = .Value
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"quartz" wrote in message
...
I have a sheet that has about 40,000 rows and each row contains
a formula in
column "W" (23). I need to convert the formula to values. My
current code
follows:

ActiveSheet.UsedRange.Columns(23).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Although the above works, I'm sure there is a more efficient
method that
could be written in a single line of code. Can anyone help me
out?

Thanks much in advance.

Thanks.

"Frank Kabel" wrote:

Hi
with
ActiveSheet.UsedRange.Columns(23)
.value=.value
end with

"quartz" wrote:

I have a sheet that has about 40,000 rows and each row contains a formula in
column "W" (23). I need to convert the formula to values. My current code
follows:

ActiveSheet.UsedRange.Columns(23).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Although the above works, I'm sure there is a more efficient method that
could be written in a single line of code. Can anyone help me out?

Thanks much in advance.