How to remove formulas without copy & paste values or VBA

Resolved · Medium Priority · Version 2013

Mon 9 Apr 2018, 18:51 Delegate Tsudoi said...

Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course

How to remove formulas without copy & paste values or VBA

Hi,
This may be classic one but could not find in Google search..
How to remove only formulas while keeping the values in selected cells. Cannot do copy&paste values because they are multiple-selected cells and I cannot use VBA. So I just select multiple cells and clear only the formulas and leave the values afterwards.
Does anyone know any way to do it...or maybe a free third party Add-In app I can use...?

Thanks,

Mon 9 Apr 2018, 20:42 Edited on Mon 9 Apr 2018, 20:42 Trainer Jens said...

RE: How to remove formulas without copy & paste values or VBA

Hi Tsudoi,

Thank you for the forum question.

I do not know any free third party Add-In app you can use.

You can copy and paste values or use VBA.

The vba code to do it is here below. You only have to amend it if your table doesn't start from A1. Each time there is A1 in the code you will need to change it to the first cell in your table.

I know that you asked for a non vba solution and you do not want to copy and paste, but I am afraid that there is no other solutions available.

Sub RemoveCalc()
Dim varArray As Variant
varArray = Range("a1").CurrentRegion
Range("a1").Resize(UBound(varArray, 1), UBound(varArray, 2)) = varArray
End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 16 Apr 2018: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Sorting List Subtotals

If you find that you would prefer to show the items in a subtotalled list in a different order, eg ascending rather than descending, you can sort your list. To sort a subtotalled list, hide the detail rows and then sort the subtotal rows. When you sort a subtotalled list, the hidden detail rows are automatically moved with the subtotal rows.
IMPORTANT: If you do not hide the details rows before sorting a subtotalled list, your subtotals will be removed and all of the rows in your list will be reordered.

View all Excel hints and tips