使用列引用清除 VBA 中单元格的内容

Clear contents of cells in VBA using column reference

提问by dojogeorge

I am trying to get a piece of code to clear the data in some cells, using the column references. I am using the following code:

我正在尝试使用列引用获取一段代码来清除某些单元格中的数据。我正在使用以下代码：

Worksheets(sheetname).Range(.Cells(2, LastColData), .Cells(LastRowData, LastColData)).ClearContents

To do this, however I am getting an error at the first .Cells section, why is this?

要做到这一点，但是我在第一个 .Cells 部分遇到错误，这是为什么？

回答by Sam

You can access entire column as a range using the Worksheet.Columnsobject

您可以使用Worksheet.Columns对象访问整个列作为范围

Something like:

就像是：

Worksheets(sheetname).Columns(1).ClearContents 

should clear contents of A column

应该清除 A 列的内容

There is also the Worksheet.Rowsobject if you need to do something similar for rows

Worksheet.Rows如果您需要对行做类似的事情，也有对象

---

---

The error you are receiving is likely due to a missing with block.

您收到的错误可能是由于缺少块。

You can read about with blocks here: Microsoft Help

您可以在此处阅读有关块的信息：Microsoft 帮助

回答by Eric Sloan

For anyone like me who came across this and needs a solution that doesn't clear headers, here is the one liner that works for me:

对于像我这样遇到此问题并需要不清除标题的解决方案的人，这里有一个适合我的班轮：

ActiveSheet.Range("A3:A" & Range("A3").End(xlDown).Row).ClearContents

Starts on the third row - change to your liking.

从第三行开始 - 根据您的喜好进行更改。

回答by Don Leverton

I just came up with this very simple method of clearing an entire sheet.

我刚刚想出了一个非常简单的清除整张纸的方法。

Sub ClearThisSheet()

ActiveSheet.UsedRange.ClearContents

End Sub

回答by Instant Breakfast

As Gary's Student mentioned, you would need to remove the dot before Cellsto make the code work as you originally wrote it. I can't be sure, since you only included the one line of code, but the error you got when you deleted the dots might have something to do with how you defined your variables.

正如 Gary 的学生所提到的，您需要先删除点才能Cells使代码按照最初编写的方式工作。我不能确定，因为您只包含了一行代码，但是删除点时出现的错误可能与您定义变量的方式有关。

I ran your line of code with the variables defined as integers and it worked:

我用定义为整数的变量运行了你的代码行，它工作了：

Sub TestClearLastColumn()

    Dim LastColData As Long
        Set LastColData = Range("A1").End(xlToRight).Column

    Dim LastRowData As Long
        Set LastRowData = Range("A1").End(xlDown).Row

    Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData)).ClearContents

End Sub

I don't think a Withstatement is appropriate to the line of code you shared, but if you were to use one, the Withwould be at the start of the line that defines the object you are manipulating. Here is your code rewritten using an unnecessary Withstatement:

我认为With语句不适合您共享的代码行，但是如果您要使用一个语句，则该语句With将位于定义您正在操作的对象的行的开头。这是使用不必要的With语句重写的代码：

With Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData))
    .ClearContents
End With

Withstatements are designed to save you from retyping code and to make your coding easier to read. It becomes useful and appropriate if you do more than one thing with an object. For example, if you wanted to also turn the column red and add a thick black border, you might use a Withstatement like this:

With语句旨在使您免于重新键入代码并使您的代码更易于阅读。如果你对一个对象做不止一件事，它就会变得有用和合适。例如，如果您还想将列变为红色并添加粗黑边框，您可以使用如下With语句：

With Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData))
    .ClearContents
    .Interior.Color = vbRed
    .BorderAround Color:=vbBlack, Weight:=xlThick
End With

Otherwise you would have to declare the range for each action or property, like this:

否则，您必须为每个操作或属性声明范围，如下所示：

    Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData)).ClearContents
    Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData)).Interior.Color = vbRed
    Worksheets("Sheet1").Range(Cells(2, LastColData), Cells(LastRowData, LastColData)).BorderAround Color:=vbBlack, Weight:=xlThick

I hope this gives you a sense for why Gary's Student believed the compiler might be expecting a With(even though it was inappropriate) and how and when a Withcan be useful in your code.

我希望这能让您了解为什么 Gary 的学生认为编译器可能期望 a With（即使它不合适）以及 a 如何以及何时With在您的代码中有用。

回答by Rav

The issue is not with the with statement, it is on the Range function, it doesn't accept the absolute cell value.. it should be like Range("A4:B100").. you can refer the following thread for reference..

问题不在于 with 语句，它在 Range 函数上，它不接受绝对单元格值..它应该像 Range("A4:B100".. 你可以参考以下线程以供参考。 .

following code should work.. Convert cells(1,1) into "A1" and vice versa

以下代码应该可以工作.. 将单元格（1,1）转换为“A1”，反之亦然

LastColData = Sheets(WSNAME).Range("A4").End(xlToRight).Column
            LastRowData = Sheets(WSNAME).Range("A4").End(xlDown).Row
            Rng = "A4:" & Sheets(WSNAME).Cells(LastRowData, LastColData).Address(RowAbsolute:=False, ColumnAbsolute:=False)

 Worksheets(WSNAME).Range(Rng).ClearContents

回答by Gary's Student

You need a Withstatement prior to this. Or make the .Cellsinto Cells

在此之前，您需要一个With语句。或者将.Cells变成Cells

回答by Kitten

To clear all rows that have data I use two variables like this. I like this because you can adjust it to a certain range of columns if you need to. Dim CRow As Integer Dim LastRow As Integer

为了清除所有有数据的行，我使用了两个这样的变量。我喜欢这样，因为如果需要，您可以将其调整为特定范围的列。将 CRow 调暗为整数 将 LastRow 调暗为整数

CRow = 1
LastRow = Cells(Rows.Count, 3).End(xlUp).Row

Do Until CRow = LastRow + 1
    Cells(CRow, 1).Value = Empty
    Cells(CRow, 2).Value = Empty
    Cells(CRow, 3).Value = Empty
    Cells(CRow, 4).Value = Empty
    CRow = CRow + 1
Loop

回答by Zapata

I found this an easy way of cleaning in a shape between the desired row and column. I am not sure if this is what you are looking for. Hope it helps.

我发现这是一种清洁所需行和列之间形状的简单方法。我不确定这是否是您要查找的内容。希望能帮助到你。

Sub sbClearCellsOnlyData()
Range("A1:C10").ClearContents
End Sub