Copy data from Range in one sheet to another

[hip2b2]

I can delete the marked line and the code seems to properly run. Is the code formatted as it should be????

'Delete Consecutive expty Rows
    Dim i As Long, lr1 As Long '<<< This line does not seem to impact the code
    lr1 = Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To lr
    If Range("B" & i).Value Like "Require" & "*" & "to run" And Range("B" & i - 1).Value = "" Then
    Cells(i, 2).EntireRow.Delete
    End If
    Next i
    End With
'End

Thanks hip

 

[sericom]

This line

For i = 2 To lr

should probably be

For i = 2 To lr1

 

[JLGWhiz]

The Dim statements are to reserve memory for the variables. They can affect the code if you try to use one of the variables in a manner different than what you have declared them for. I have taken the liberty of changing some of the code. See red font. When deleting rows, it is better to work from bottom to top with the loop so it will not skip any rows when evaluating the criteria. That said, the thread title was for copying from one sheet to another, but this code does not execute any copy command. You should make sure your thread title represents what your issue is.

Dim i As Long, lr1 As Long '<<< This line does not seem to impact the code
    lr1 = Cells(Rows.Count, 2).End(xlUp).Row
       For i = lr1 To 2 Step -1
           If Range("B" & i).Value Like "Require" & "*" & "to run" And Range("B" & i - 1).Value = "" Then
              Rows(i).Delete
          End If
      Next i
End With

 

[hip2b2]

The Dim statements are to reserve memory for the variables. They can affect the code if you try to use one of the variables in a manner different than what you have declared them for. I have taken the liberty of changing some of the code. See red font. When deleting rows, it is better to work from bottom to top with the loop so it will not skip any rows when evaluating the criteria. That said, the thread title was for copying from one sheet to another, but this code does not execute any copy command. You should make sure your thread title represents what your issue is.

Dim i As Long, lr1 As Long '<<< This line does not seem to impact the code
    lr1 = Cells(Rows.Count, 2).End(xlUp).Row
       For i = lr1 To 2 Step -1
           If Range("B" & i).Value Like "Require" & "*" & "to run" And Range("B" & i - 1).Value = "" Then
              Rows(i).Delete
          End If
      Next i
End With

Your revised code works perfectly, I will assume that it is more efficient than the "dog's breakfast" I put together.

On the topic of efficiency, does this looping code start at the bottom of the worksheet data range or at the bottom of the worksheet.

Sorry about the Title mismatch, I started one question and changed it to another and forgot the title. (an explanation not an excuse).

Thanks

hip

 

[JLGWhiz]

As written, the bottom of the data range.

 

[hip2b2]

Thank you for the guidance.

Hip

 

[JLGWhiz]

Thank you for the guidance.

Hip

You 're welcome,
Regards, JLG