VBA Excel：运行时错误'1004'object'_Worksheet的方法'范围'faile [英] VBA Excel: Run-time error '1004' Method 'Range' of object'_Worksheet' faile

问题描述

我试图清除从工作表1复制到工作表2的最后一行之后的50行。所以工作表2中的最后一行数据应该总是有50个空行。

I'm trying to clear the 50 rows after the last row I copy over from worksheet 1 into worksheet 2. So the last row of data in Worksheet 2 should always have 50 blank rows after it.

Public copied_row_count As Integer
Public ws1 As Worksheet
Public ws2 As Worksheet
Sub Button1_Click()

'Defining the constants below. Change if need based on the value definitions'

Dim Test_Set_Colmn_Number As Integer                                                            'Defining the value of the Cylinder Test Set column number'
Test_Set_Colmn_Number = 23                                                                      'W is the current column with the cylinder test set values'

Set ws1 = ThisWorkbook.Sheets("Concrete Log")
Set ws2 = ThisWorkbook.Sheets("Concrete Tracking Graphs")

copied_row_count = 1
Dim j As Integer
j = 0

For i = 1 To Rows.count
    If Not IsEmpty(Cells(i, Test_Set_Colmn_Number).Value) Then
        ws1.Rows(i).Copy _
        ws2.Rows(copied_row_count)
        copied_row_count = copied_row_count + 1
    End If
Next i

Do While j < 50
    ws2.Range("copied_row_count + j:copied_row_count + j").Clear
    j = j + 1
Loop

End Sub

我将错误缩小到变量
copied_row_count
及其在

I've narrowed the error down to the variable copied_row_count and its use in

ws2.Range("copied_row_count + j:copied_row_count + j").Clear

谢谢！

推荐答案

您在引号内使用变量名，因此它们被视为文字文字。

You're using variable names inside the quotes, so they are treated as literal text.

如果您要清除（copied_row_count + j）的行，可以使用以下行：

If you're looking to clear the rows at (copied_row_count + j), you can use this line:

ws2.Rows(copied_row_count + j).ClearContents

这篇关于VBA Excel：运行时错误'1004'object'_Worksheet的方法'范围'faile的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！