Application-defined or object-defined error

[Heather_Llo]

Hi,

I am getting said error on this line:

.EntireRow.Cut Destination:=Sheets("Need to E-mail").Range("B" & Rows.Count).End(xlUp).Offset(1, -2)

Of this macro:

Sub NeedEmail()Dim LR As Long, i As Long
With Sheets("Working")
LR = .Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("B" & i)
If .Value Like "*Interested*" Then .EntireRow.Cut Destination:=Sheets("Need to E-mail").Range("B" & Rows.Count).End(xlUp).Offset(1, -2)
End With
Next i
End With
End Sub

That I jacked from this topic: https://www.mrexcel.com/forum/excel-questions/685654-simple-macro-cut-paste-based-cell-value.html

I am wondering if anyone can help me to diagnose the problem. I'm new to Excel and not entirely sure what I'm doing. Learning steadily but slowly.

Thanks!

 

[Fluff]

this

.Offset(1, -2)

is trying to offset 2 columns to the left, but as you are looking at col B, you can only offset 1 column left.
What are you trying to do?

 

[Heather_Llo]

Changing that to -1 worked, thank you so much! Simple errors due to my simple misunderstanding, lol.

I am cutting any row in sheet "Working" that has "Need to E-mail" in Column B and pasting it into the next blank row on a new sheet called "Need to E-mail".

Since this is working now, I have another question: How can I modify the code to delete the blank rows after I've cut their content? Should I just record a macro where I filter on blank rows and delete them that way?

 

[Fluff]

I'd tend to do it like this

Sub NeedEmail()
   Dim LR As Long, i As Long
   Dim Rng As Range
   
   With Sheets("Working")
      LR = .Range("B" & Rows.Count).End(xlUp).Row
      For i = 1 To LR
         If .Range("B" & i).Value Like "*Interested*" Then
            .Rows(i).Copy Sheets("Need to E-mail").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
            If Rng Is Nothing Then
               Set Rng = .Range("A" & i)
            Else
               Set Rng = Union(Rng, .Range("A" & i))
            End If
         End If
      Next i
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

 

[Heather_Llo]

Thank you so much. you're a lifesaver.

 

[Fluff]

Glad to help & thanks for the feedback