清除所有工作表内容的最快方法 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29967411/
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
Quickest way to clear all sheet contents VBA
提问by hc91
I have a large sheet that I need to delete all the contents of. When I try to simply clear it without VBA it goes into not responding mode. When using a macro such as:
我有一张大表，需要删除其中的所有内容。当我尝试在没有 VBA 的情况下简单地清除它时，它会进入无响应模式。使用宏时，例如：
Sub ClearContents ()
Application.Calculation = XlManual
Application.ScreenUpdating = False
Sheets("Zeroes").Cells.ClearContents
Application.ScreenUpdating = True
End Sub
It also doesn't respond. What's the quickest way to do this?
它也没有反应。执行此操作的最快方法是什么？
回答by Comintern
The .Cells range isn't limited to ones that are being used, so your code is clearing the content of 1,048,576 rows and 16,384 columns - 17,179,869,184 total cells. That's going to take a while. Just clear the UsedRange instead:
.Cells 范围不限于正在使用的范围，因此您的代码正在清除 1,048,576 行和 16,384 列的内容 - 总共 17,179,869,184 个单元格。这需要一段时间。只需清除 UsedRange 即可：
Sheets("Zeros").UsedRange.ClearContents
Alternately, you can delete the sheet and re-add it:
或者，您可以删除工作表并重新添加它：
Application.DisplayAlerts = False
Sheets("Zeros").Delete
Application.DisplayAlerts = True
Dim sheet As Worksheet
Set sheet = Sheets.Add
sheet.Name = "Zeros"
回答by Stax
Technically, and from Comintern's accepted workaround,
I believe you actually want to Delete all the Cells in the Sheet. Which removes Formatting (See footnote for exceptions), etc. as well as the Cells Contents.
I.e. Sheets("Zeroes").Cells.Delete
从技术上讲，从共产国际接受的解决方法来看，我相信您实际上想要删除工作表中的所有单元格。这将删除格式（有关例外情况，请参见脚注）等以及单元格内容。IE Sheets("Zeroes").Cells.Delete
Combined also with UsedRange, ScreenUpdating and Calculation skipping it should be nearly intantaneous:
还结合 UsedRange、ScreenUpdating 和 Calculation 跳过，它应该几乎是瞬时的：
Sub DeleteCells ()
Application.Calculation = XlManual
Application.ScreenUpdating = False
Sheets("Zeroes").UsedRange.Delete
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
Or if you prefer to respect the Calculation State Excel is currently in:
或者，如果您更愿意尊重 Excel 当前的计算状态：
Sub DeleteCells ()
Dim SaveCalcState
SaveCalcState = Application.Calculation
Application.Calculation = XlManual
Application.ScreenUpdating = False
Sheets("Zeroes").UsedRange.Delete
Application.ScreenUpdating = True
Application.Calculation = SaveCalcState
End Sub
Footnote: If formatting was applied for an Entire Column, then it is not deleted. This includes Font Colour, Fill Colour and Borders, the Format Category (like General, Date, Text, Etc.) and perhaps other properties too, but
脚注：如果对Entire Column应用了格式，则不会将其删除。这包括字体颜色、填充颜色和边框、格式类别（如常规、日期、文本等）以及其他可能的属性，但
Conditional formatting IS deleted, as is Entire Rowformatting.
删除了条件格式，就像整行格式一样。
(Entire Columnformatting is quite useful if you are importing raw data repeatedly to a sheet as it will conform to the Formats originally applied if a simple Paste-Values-Only type import is done.)
（如果您将原始数据重复导入到工作表中，则整列格式非常有用，因为如果完成了简单的仅粘贴值类型的导入，它将符合最初应用的格式。）
回答by Jones
You can use the .Clear method:
您可以使用 .Clear 方法：
Sheets("Zeros").UsedRange.Clear
Using this you can remove the contentsand the formattingof a cell or range without affecting the rest of the worksheet.
使用此功能，您可以删除单元格或区域的内容和格式，而不会影响工作表的其余部分。
回答by Zernel
Try this one:
试试这个：
Sub clear_sht
Dim sht As Worksheet
Set sht = Worksheets(GENERATOR_SHT_NAME)
col_cnt = sht.UsedRange.Columns.count
If col_cnt = 0 Then
col_cnt = 1
End If
sht.Range(sht.Cells(1, 1), sht.Cells(sht.UsedRange.Rows.count, col_cnt)).Clear
End Sub
