Geek Answers Handbook

VBA for a clear value in a certain range of cells and a protected cell from leaching formula

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

And then, how do I lock a cell to prevent it from being fixed or cleaned up?

+6
source share
3 answers

You can define a macro containing the following code:

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

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

To leave your formulas unchanged, use:

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

First, you select the general range of cells that you are interested in clearing the contents of, and then further restrict the selection to only include cells that contain what, according to excel, are “constants”.

You can do this manually in excel by selecting a range of cells, pressing "f5" to open the "Go To" dialog box, then click on the "Special" button and select the "Constants" option and click "OK '.

+12
source

try it

 Sheets("your sheetname").range("A5:X50").Value = ""

You can also use

 ActiveSheet.range
+3
source

Not sure if faster with VBA is the fastest way to do this in a regular Excel program:

  • Ctrl-G
  • A1:X50 Enter
  • Delete

If you do not need to do this very often, entering and then launching VBA code is more effort.

And if you only want to delete formulas or values, you can paste Ctrl-G, Alt-S to select Goto Special, and here select "Formulas or Values."

+2
source

More articles:


All Articles