清除所有表格内容VBA的最快方法 [英] Quickest way to clear all sheet contents VBA

问题描述

我有一张大的表单，我需要删除所有的内容。当我尝试简单地清除它没有VBA它进入不响应模式。当使用宏如：

  Sub ClearContents（）
 Application.Calculation = XlManual 
应用程序。 ScreenUpdating = False 
 
 Sheets（Zeroes）。Cells.ClearContents 
 
 Application.ScreenUpdating = True 
 End Sub 
  

它也没有响应。什么是最快的方法？

解决方案

.Cells范围不限于正在使用的范围，所以您的代码正在清除1,048,576行和16,384列的总数 - 17,179,869,184个单元格的内容。这将需要一段时间。只需清除UsedRange：

  Sheets（Zeros）。UsedRange.ClearContents 
  

或者，您可以删除该表并重新添加：

  Application.DisplayAlerts = False 
表格（Zeros）。删除
 Application.DisplayAlerts = True 
 Dim sheet As Worksheet 
 Set sheet = Sheets.Add 
 sheet.Name =Zeros
  

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:

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?

解决方案

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:

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"

这篇关于清除所有表格内容VBA的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！