CODEWITHSUNDEEP
vbaexcel
Noah Yang
Noah Yang

Reputation: 5

VBA Excel: Run-time error '1004' Method 'Range' of object'_Worksheet' faile

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

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

Thanks!

Upvotes: 0

Views: 393

Answers (3)

BigBobby
BigBobby

Reputation: 443

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

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

Upvotes: 1

Sobigen
Sobigen

Reputation: 2169

The way you've written your range lookup is to look for a range literally labeled "copied_row_count + j:copied_row_count + j". You aren't using the variable if it's in quotes like that. I think you could replace your whole loop actually.

Remove the Do loop and try this line

ws2.Range(copied_row_count & ":" & copied_row_count + 50)

Upvotes: 0

Matteo NNZ
Matteo NNZ

Reputation: 12645

This should be a composition of variables and strings: 

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

Like before (ws2.Range("copied_row_count + j:copied_row_count + j").Clear), you're literally searching the range copied_row_count & j & : & copied_row_count + j which clearly does not exist --> Object defined error.

Upvotes: 0

Related Questions