VBA在特定的细胞范围内具有明确的价值，保护细胞不被洗去配方

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 ?

我有A1:Z50之类的数据，但我只想使用VBA删除A5:X50(我认为这比拖拽整个单元或者使用clickA5+shift+clickX50+delete要快得多)。我该怎么做呢?

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

然后，如何锁定单元以防止它被修复或清除?

3 个解决方案

#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.

运行宏将在活动工作表上选择范围A5:x50，并清除该范围内单元格的所有内容。

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.'

这将首先选择您想要清除内容的单元格的整体范围，然后进一步将选择限制为只包含包含excel认为是“常量”的单元格。

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'.

在excel中，你可以通过选择单元格的范围，点击“f5”打开“Go to”对话框，然后点击“Special”按钮，选择“constant”选项，点击“Ok”。

#2

Try this

试试这个

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

You can also use

您还可以使用

ActiveSheet.range

#3

Not sure its faster with VBA - the fastest way to do it in the normal Excel programm would be:

不确定VBA会更快——在正常的Excel程序中，最快的方法是:

Ctrl-G
Ctrl-G
A1:X50 Enter
A1:×50进入
Delete
删除

Unless you have to do this very often, entering and then triggering the VBAcode is more effort.

除非您必须经常这样做，否则进入VBAcode并触发它需要更多的努力。

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

如果您只想删除公式或值，您可以插入Ctrl-G, Alt-S来选择Goto Special，这里选择公式或值。

#1