How to: Quick deleting formulae w/o affecting cells' result

Don Juan

New Member
Joined
Sep 26, 2004
Messages
17
I have massive ranges of cells with formula.

Need to make quick delete on ALL but without affecting the calculation result.


Please help.


Don
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
If I understand your matter, try this:

Select all cells and paste special values: formulas are deleted and results are preserved.

If my suggestion doesn't point in the right direction, post an example of what you need.

Ciao
 

Don Juan

New Member
Joined
Sep 26, 2004
Messages
17
Caro Chiello,

Exacto, just fit.
Mucho gracias.

RE: Saving data from refreshing range on same sheet

I have a refreshing range c5:D18 downloaded from web,
Refreshing every 60 minutes.

Wish to copy and be saved to G5:H18 after first 60 minutes,
then to J5:K18 after other 2x60 minutes, and so on.

i.e. original data at left be saved to the right, with an empty column in between.

Any macro you suggest ?

Saludos
Don
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
Run the following code to start to save from column G, then J, and so on.

Code:

Public SaveColumn As Long
Public Sub SaveRefreshedData()
If SaveColumn = 0 Then
SaveColumn = 7
ElseIf SaveColumn >= 256 Then
Exit Sub 'Stop save
SaveColumn = 7 'Overwrite
End If
Sheets("Sheet1").Range("C5:D18").Copy Destination:= _
Sheets("Sheet1").Range(Cells(5, SaveColumn), Cells(18, SaveColumn + 1))
'Application.OnTime Now + TimeValue("01:00:00"), "SaveRefreshedData"
SaveColumn = SaveColumn + 3
End Sub


If you want the macro runs automatically as you open your book, add the following code to the Thisworkbook code module

Private Sub Workbook_Open()
SaveRefreshedData
End Sub



Post for feedback


Ciao
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
chiello said:
Run the following code to start to save from column G, then J, and so on.

Code:

Public SaveColumn As Long
Public Sub SaveRefreshedData()
If SaveColumn = 0 Then
...
ElseIf SaveColumn >= 256 Then
Exit Sub 'Stop save
SaveColumn = 7 'Overwrite
End If
...
'Application.OnTime Now + TimeValue("01:00:00"), "SaveRefreshedData"
...
End Sub

I commented the wrong statement!!

Uncomment 'Application.OnTime Now + TimeValue("01:00:00"), "SaveRefreshedData"


Comment one of the 2
Exit Sub 'Stop save when reached the last useful column
SaveColumn = 7 'Overwrite old values

Ciao
 

Watch MrExcel Video

Forum statistics

Threads
1,122,331
Messages
5,595,549
Members
413,996
Latest member
mabelO

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