VBA error: Error 1004: Error Method range of object _worksheet failed

1
votes

I am trying to create VBA for this function - to delete rows if cell in column A contains "BG01". Header name for column A is "Payment Center"

But when I run the macro, it shows: Error Method range of object _worksheet failed 

row_number = 1

Do
    DoEvents
    row_number = row_number + 1
    Payment_Center = Sheet1.Range("A" & row_number) ' <~~~~~ Error occurs here

    If InStr(Payment_Center, "BG01") >= 1 Then
        Sheet1.Rows(row_number & ":" & row_number).Delete
        row_number = row_number - 1
    End If

Loop Until Payment_Center = " "
1
vba

1 Answers

0
votes

The error happens because the range object needs to be modified using the Set keyword.

Set Payment_Center = Sheet1.Range("A" & row_number)

and your loop should look like

row_number = 1

Do
  DoEvents
  row_number = row_number + 1
  Set Payment_Center = Sheet1.Range("A" & row_number) '

  If InStr(Payment_Center, "BG01") >= 1 Then
    Sheet1.Rows(row_number & ":" & row_number).Delete
    row_number = row_number - 1

  End If

  ' reset Payment_Center range using new row number
  set Payment_Center = Sheet1.Range("A" & row_number)

Loop Until Payment_Center = " "

and it should work

it would also help your coding and debugging if you had Option Explicit at the top of all your code modules, and declared all variables properly, such as 

Dim row_number As Integer
Dim Payment_Center as Range