VBA code works only in the debugging mode and hangs when running in normal mode

Question

Hi, This problem is driving me crazy. I have the following code:

'unprotect sheet
If.Range("Start").Row+1<.Range("End").Row then
  .Rows(.Range("Start").Row+1 & ":" & .Range("End").Row-1).Select
  Selection.Delete Shift:=xlUp
  'protect sheet
End if

when I run it in debugging mode and trace the code, it works perfectly. But when run the code in a normal mode (not debugging) it gives me an error message as " select method of Range class failed" This errors happens in the line: .Rows(.Range("Start").Row +1 .... that is just after the IF statement. Any ideas? Please help.

Answer 1

This error usually means you are trying to select a range that belongs to a non-active sheet.

You almost always don't need to select anything:

.Rows(.Range("Start").Row+1 & ":" & .Range("End").Row-1).Delete Shift:=xlUp