Improve Efficiency of VBA Code

[Paul_Hossler]

Well, I guess I'm not understanding where you want to go

You can try this and see if it's more to your liking. Also takes about a second to run, but once the formulas are replaced with values, anything that depends on a Dropdown seems like it'd be wrong

It seems like it has the same functional layout as your originally did

Option Explicit


Sub test2()
    
    Dim rAll As Range, rForm As Range, rData As Range, rFormulas As Range, rCell As Range
    
    Set rAll = ActiveSheet.Cells(6, 1).CurrentRegion
    Set rForm = rAll.Rows(1)
    Set rData = rAll.Cells(3, 1).Resize(rAll.Rows.Count - 2, rAll.Columns.Count)
    Set rFormulas = rForm.SpecialCells(xlCellTypeFormulas)
    
    Application.ScreenUpdating = False
    
    For Each rCell In rFormulas.Cells
        rCell.Copy rData.Columns(rCell.Column)
        rData.Columns(rCell.Column).Value = rData.Columns(rCell.Column).Value
    Next
    
    Application.CutCopyMode = False


    Application.ScreenUpdating = True
    
    MsgBox "Done"




End Sub