Final lines of code are ignored - can't understand why

I am using the following code to insert blank rows. By starting at the bottom of column H and stepping upward one cell at a time, i compare that cell to the one below it. If they are not identical, I insert a blank row between them. This is the code:

Please Login or Register  to view this content.

This executes without a problem, working as it is intended. However there is a small portion of code after it that does not seem to execute. It looks at the blank rows and fills them with no-fill in case they may contain color from previous instructions. When I run this as a stand-alone routine, it works as intended. But it will not run in the sub routine that contains the code above that inserts blank rows. I have never used the "GoTo" command, and I am wondering if it is causing problems.

Please Login or Register  to view this content.

"IF" has two formats:

1.)
If x = y Then
'do stuff
End If

2.)
If x = y Then 'do stuff


If you include actions on the same line proceeding the Then, everything else is ignored.

Please Login or Register  to view this content.

I'm not sure how this isn't throwing ElseIf without If compiling error.

My mistake for not including a piece of code that was important. I removed an instruction that I thought would be confusing. It was a way to exit the subroutine when the iteration upward reached the header. Complete code for inserting blank rows below:

Please Login or Register  to view this content.

You should place all of your tags at the very end of your script, and manually add an Exit Sub before them.

Your script never gets past GetNext because it is outside of the IF. There is no circumstance when it doesn't hit it.

Examine this logic as an alternative:

Please Login or Register  to view this content.

Now my code can end before it gets to GetNext. In your code, even when GetNext doesn't apply it always runs into it.

The difference is near the top, row 2. In before, it is yellow; in after, it's not filled.

Wedge120-before.xls
Wedge120-after.xls


Here is the code as I currently have it written:

Please Login or Register  to view this content.

Here a 2-birds-with-one stone approach. It places the tag at the end and gets rid of excess code. Still, if there are better ways, do share:

Please Login or Register  to view this content.

Can you attach a sample worksheet that this applies to, with some dummy data?

From post #4, I would re-write this. Judicious use of GoTo acceptable, but too often it is overused leading to confusion.

Please Login or Register  to view this content.

Originally Posted by Wedge120

Is there EVER a time that "goto" should really be used?

The "best practices" police will scream every time that they see a GoTo.
There is always a work-around so one can avoid using it. (Often involving introducing a boolean variable)*

The situation that I see where GoTo is appropriate is a looping AND.
Consider the problem. "If every worksheet in a book has 1 in A1, add a new worksheet to the book, otherwise do nothing"
One way to code that would be

Please Login or Register  to view this content.

More important (IMO) than the lexical rule "don't use GOTO" is the conceptual rule "One Way In, One Way Out"
A procedure should only have one start point and all the branching should combine at the end so the End Sub line is actually excited. (Except for error conditions).
They both lead away from "spaghetti code" where you can't tell what is going on next.

*- This is a non-goto code that solves the above problem

Please Login or Register  to view this content.

I think the first code is easier to understand (especially in cases where the condition is more complicated).

The guide should be, will you understand your code in six months when you revisit it to change something? Will some stranger whose never seen it before be able to decipher and alter it to their wishes?