This macro will convert formulas to its values until last row
Code: Select all
Sub ConvertFormulasToValues()
Dim ws As Worksheet, rng As Range, cl As Range, lr As Long, c As Long
Const fRow As Long = 6
Const sRow As Long = 8
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.EnableCancelKey = xlErrorHandler
End With
Set ws = ThisWorkbook.Worksheets("DATA")
lr = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rng = ws.Rows(fRow).SpecialCells(xlCellTypeFormulas)
For Each cl In rng
c = cl.Column
ws.Cells(fRow, c).Copy: ws.Cells(sRow, c).PasteSpecial Paste:=xlPasteFormulas
With ws.Range(ws.Cells(sRow, c), ws.Cells(lr, c))
.Formula = ws.Cells(sRow, c).Formula
.Value = .Value
End With
Next cl
With Application
.CutCopyMode = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub
so I am searching for alternative approach using arrays for example or any other way avoiding loops
Note : I'm now working on a file with 20000 rows
any help on this is much Appreciated! Thank you in advance for looking at this.