Paste values macro

[Tony]

I have 500 rows with formulas in them, some of which have calculated values
and therefore there is data sitting in them.

I would like a macro to paste the values of the rows in which data has been
calculated, leaving the uncalculated rows with the same formula in them.

I then need to delete entire rows of the pasted values if a date value
exists in column K.

I have been ripping my hair out over this so any help would be much
appreciated.

Thanks

 

[Carim]

Hi Tony

Is there a way you can differentiate your uncalculated rows from your
calculated rows when both of these rows have cells which all contain
formulas ?

Carim

 

[Tom Ogilvy]

if the calculate values are numbers and the uncalculated values are text

for example they may contain a formula like

=if(A1<>"",A1*20,"")

then you can do something like:

Sub FixFormulas()
Dim rng As Range
On Error Resume Next
Set rng = Cells.SpecialCells(xlFormulas, xlNumbers)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cell In rng
cell.Formula = cell.Value
Next
End If
End Sub

'for deleting rows

Sub DeleteRows()
Dim lastrow As Long, i As Long
Dim rng As Range
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
If IsDate(Cells(i, "K")) Then
On Error Resume Next
Set rng = Rows(i).SpecialCells(xlFormulas)
On Error GoTo 0
If rng Is Nothing Then
Rows(i).Delete
End If
End If
Next
End Sub