Help With Macro or VBA for "setting data".

[PolarBear71]

This is my first time posting here. I searched for posts that were similar to mine, but may not have navigated the forums as well as I should have. The assistance I require is this:

I created a spreadsheet that allows the user to paste in data from five other reports and then it uses array formulas and/or indirect worksheets to format the information on different worksheets in a way that is presentable to clients. The sheets could have anywhere from 10 rows to 50000 rows. While I'm fairly good at Excel formulas, I am not great at macros/VBA. Is there a way to program it so that the data that is referenced is pasted as data and any formulas that do not return data are deleted? I would then end by turning each page of data into a table. I haven't been able to figure out a way to delete all formulas below where any data is returned. If I just program it to copy the whole sheet and paste back as a value, then programming it to make tables includes all those blank cells (where my formula enters "" if not data is returned). Even a hint in the right direction is appreciated. Thank you!

 

[Marcelo Branco]

Welcome to Mr Excel

Try it in a copy of your workbook

Assuming Sheet1 as the sheet of interest see if this does what you need

Sub Test()
    With Sheets("Sheet1").UsedRange
        .Value = .Value
    End With
End Sub

Hope this helps

M.