vba VBA在特定单元格范围内清除值并保护单元格不被冲走公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14394883/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA for clear value in specific range of cell and protected cell from being wash away formula
提问by eathapeking
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 ?
然后，如何锁定单元格以防止它被修复或清除？
回答by Glenn Stevens
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”以显示“转到”对话框，然后单击“特殊”按钮并选择“常量”选项并单击“确定”。
回答by Niklas
Try this
尝试这个
Sheets("your sheetname").range("A5:X50").Value = ""
You can also use
你也可以使用
ActiveSheet.range
回答by Peter Albert
Not sure its faster with VBA - the fastest way to do it in the normal Excel programm would be:
不确定使用 VBA 会更快 - 在普通 Excel 程序中最快的方法是：
Ctrl-GA1:X50 EnterDelete
Ctrl-GA1:X50 EnterDelete
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-Sto select Goto Special and here select Formulas or Values.
如果您只想删除公式或值，您可以插入Ctrl-G, Alt-S以选择 Goto Special，然后在此处选择公式或值。
