Change any cell Formula contain paste value

[Abhijeet]

Hi

I have data in excel in that many cells contain formula i want paste value i do not want to show formula all dates do not want to change the format of that cells.

 

[Khalid NGO]

Hi Abhijeet,

Select your range i.e. A1:F5
Press Ctrl+C (for copy)

Press Ctrl+Alt+V (for Paste Special)

Press V (for selecting values only)

Press Enter

Regards,

 

[Abhijeet]

But in that range Date & i do not want to change the date format

 

[NARAYANK991]

Hi Abhijeet ,

What will be the values in the data range ? Will they be less than , say 41000 ?

Will the dates all be later than 1/1/2013 ?

If so , after doing what Khalid has suggested , you can apply the following format to the entire data range :

[<41000]General;[>=41000]dd-mmm-yyyy

This will ensure that the dates are displayed in your format , while the values are displayed as numbers.

Narayan

 

[Abhijeet]

No data is huge & dates are different column so this is very huge process

 

[Abhijeet]

Narayan sir please give me file as per your comment

 

[shrivallabha]

Hi Abhijeet,

You can try below code. Just stay on the sheet where you want to remove formulas.

Public Sub Formulatovalues()
Dim rng As Range
Application.Calculation = xlCalculationManual
On Error Resume Next
For Each rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
  rng.Value = rng.Value
Next rng
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.CalculateFull
End Sub

 

[Abhijeet]

Thanks Both Narayan & Shrivallabha it is working