Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste values macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste values macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste values macro
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 -- Regards, Tom Ogilvy "Tony" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Cut and paste Values | Excel Discussion (Misc queries) | |||
Macro: Paste Values | Excel Discussion (Misc queries) | |||
Paste values - macro | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |