Removing formulas with VBA in excel also deleting values

[CRHAAG]

I am trying to remove all of the formulas from various sheets and just leave the values. I have tried to do this with VBA in a few different ways(below) but with each of them there are always some values that just come back as blank. Has anybody experienced this before?

    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange
        If rng.HasFormula Then
            rng.Formula = rng.Value
        End If
    Next rng

    Application.DisplayAlerts = False 'stops message box from alerting about pasting over values
    Sheets(reportArray(0)(2)).Cells.Select
    Selection.Copy
    Sheets(reportArray(0)(2)).Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.DisplayAlerts = True 'turns alerts back on

    With ActiveSheet.UsedRange
        .Value = .Value
    End With

 

[Fluff]

That last code should work & the only blanks should be where a cell was blank or had a formula that returned ""

 

[CRHAAG]

That last code should work & the only blanks should be where a cell was blank or had a formula that returned ""

Hi,

Thanks for the response. This sort of fell off my radar for a while but I am revisiting today. None of those code strings work. If I run the report without trying to get rid of the formulas, I get the following

Rows	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column
Row	$ 4,951	$ 9,600	$ (4,649)	$ 1,067	$ 375	$ 1,442	-3.22	3.57	2.47	12,779	9,630	-	9,630	#####	#####
Row	$ 5,187	$ -	$ 5,187	$ -	$ 375	$ 375	13.83	3.57	2.47	12,779	9,630	-	9,630	$ -	$ -
Total	$ 10,139	$ 9,600	$ 539	$ 1,067	$ 750	$ 1,817	0.30	7.14	4.94	25,558	19,259	-	19,259

And this is what I get when I try to get ride of the formulas with any of the code listed above

Rows	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column	Column
Row
Row	$ 5,187	$ -	$ 5,187	$ -	$ 375	$ 375	13.83	3.57	2.47	12,779	9,630	-	9,630	$ -	$ -
Total	$-	$-	$-	$-	$-	$-	#DIV/0!	-	-	-	-	-	-

 

[Fluff]

As I said, the last bit of code you posted should work.