Excel vba清除定期更改的一系列数据(Excel vba clearing a range of data that is changing regularly)

Excel vba清除定期更改的一系列数据(Excel vba clearing a range of data that is changing regularly)

我试图在excel中创建一个简单的vba函数，通过查找最后使用的行并将其设置为范围的结尾，清除从A列到K和第6行到数据底部的一系列数据。 。 这是我的代码不起作用：

Sub clearNameData() Dim destSheet As Worksheet: Set destSheet = ThisWorkbook.Worksheets("Name Search") lMaxRows = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row destSheet.range("A6:K & lMaxRows").ClearContents End Sub

当我运行此代码时，它给出了错误“运行时错误'1004'：对象'_Worksheet'的MEthod'Range'失败了”

尽管尝试了我的范围定义的每个语法组合，但我无法弄清楚如何获得我想要做的工作。 任何帮助将不胜感激！

I am trying to make a simple vba function in excel that clears a range of data, that goes from column A to K and row 6 to the bottom of the data, by finding the last used row and setting that as the end of the range. This is the code I have that is not working:

Sub clearNameData() Dim destSheet As Worksheet: Set destSheet = ThisWorkbook.Worksheets("Name Search") lMaxRows = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row destSheet.range("A6:K & lMaxRows").ClearContents End Sub

When I run this code it gives me the error "Run-time error '1004': MEthod 'Range' of object'_Worksheet' failed"

I cant figure out exactly how to get what im trying to do to work despite trying every syntax combination of my range definition. Any help would be greatly appreciated!

最满意答案

根据OP的要求，我正在将我的评论转移到答案中。

问题出在线上

destSheet.range("A6:K & lMaxRows").ClearContents

它需要改为：

destSheet.range("A6:K" & lMaxRows).ClearContents

请注意，结束引号已移动。 假设lMaxRows是500，这使得范围参数“A6：K500”，然后VBA可以将其评估为范围对象。 之前，它是文字字符串A6：KlMaxRows，VBA 无法评估范围对象，因为变量是字符串的一部分。

As requested by the OP, I'm moving my comment to the answer.

The issue was with the line

destSheet.range("A6:K & lMaxRows").ClearContents

It needed to be changed to:

destSheet.range("A6:K" & lMaxRows).ClearContents

Note that the ending quotation mark moved. Supposing that lMaxRows is 500, this makes the range argument "A6:K500", which VBA can then evaluate as a range object. Before, it was the literal string A6:KlMaxRows, which VBA is not able to evaluate to a range object, since the variable is part of a string.