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

Question

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.

Please use the Code Sample button ("1010 101") to reformat your question. It is very hard to read. — Foole
– Foole, Commented Aug 28, 2010 at 23:41

GSerg · Accepted Answer · 2018-09-27 17:30:12Z

7

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

edited Sep 27, 2018 at 17:30

answered Aug 28, 2010 at 23:48

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

guest1 Over a year ago

Perfect! It worked. So, what I did, whenever I select a range anywhere in the code, I activate the corresponding sheet first then I select. Thanks a lot! It was very helptul!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related