Copy + Paste column formulas, ignoring #N/A

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
840
Hello,

I need VBA code please that will copy + paste the formulas (removing the formulas), except for cells with "#N/A".

Many thanks.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Not sure if I've understood you correctly, but how about this
Code:
Sub RemoveFormulae()

   With Columns(3).SpecialCells(xlFormulas, xlTextValues + xlNumbers)
      .Value = .Value
   End With
End Sub
This will convert the formulae in col C to values, ignoring the #N/A
 
Upvote 0
Not sure if I've understood you correctly, but how about this
Code:
Sub RemoveFormulae()

   With Columns(3).SpecialCells(xlFormulas, xlTextValues + xlNumbers)
      .Value = .Value
   End With
End Sub
This will convert the formulae in col C to values, ignoring the #N/A


Hello Fluff,

Seems to work many thanks for this.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Glad to help & thanks for the feedback

No worries, just encountered a slight problem.

When it gets to the first set of #N/As, following that it then pastes what was in the first row - row 3.



So for example:

151
151
151
151
#N/A
#N/A
#N/A
826 (should have been this, but actually returns 151).

Not sure why it is doing that?

Thanks for any help?
 
Upvote 0
My mistake, try this
Code:
Sub RemoveFormulae()

   Dim Rng As Range
   For Each Rng In Columns(3).SpecialCells(xlFormulas, xlTextValues + xlNumbers).Areas
      Rng.Value = Rng.Value
   Next Rng
End Sub
 
Upvote 0
My mistake, try this
Code:
Sub RemoveFormulae()

   Dim Rng As Range
   For Each Rng In Columns(3).SpecialCells(xlFormulas, xlTextValues + xlNumbers).Areas
      Rng.Value = Rng.Value
   Next Rng
End Sub

Thanks Fluff, seems to work, would it be quicker if I specified a range too? Say C3:C100000?

Many thanks.
 
Upvote 0
Give it a go & see.
I wouldn't expect any real difference in speed as SpecialCells only works on the UsedRange.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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
Back
Top