VBA for clear value in specific range of cell and protected cell from being wash away formula

Question

I have data from like A1:Z50 but I want to delete only A5:X50 using VBA (I think it will be a lot faster than dragging the whole cell or using clickA5+shift+clickX50+delete). How can I do this ?

And then, how to lock the cell to prevent it from getting fixed or cleared ?

Answer 1

You could define a macro containing the following code:

Sub DeleteA5X50()   
    Range("A5:X50").Select
    Selection.ClearContents
end sub

Running the macro would select the range A5:x50 on the active worksheet and clear all the contents of the cells within that range.

To leave your formulas intact use the following instead:

Sub DeleteA5X50()   
    Range("A5:X50").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
end sub

This will first select the overall range of cells you are interested in clearing the contents from and will then further limit the selection to only include cells which contain what excel considers to be 'Constants.'

You can do this manually in excel by selecting the range of cells, hitting 'f5' to bring up the 'Go To' dialog box and then clicking on the 'Special' button and choosing the 'Constants' option and clicking 'Ok'.