How to skip special cases in a For Each loop

So I'm using a For Each loop to go down column A and apply statements to each of the cells with data in it.

However, I need to the For Each loop to skip the first row of data (the titles), to skip any blank rows in between data used as visual separators, and to stop the loop when it reaches the last row of data in that column.

Here's what I have:

For Each ProductID In [2011-liftgate-price-update-sam.xlsm].Worksheets("Sheet1").Range("A:A")

'If Then Statement is used to filter out rows that dont have Product data
    If WorksheetFunction.Row(ProductID) = 1 Then Next ProductID
        ElseIf WorksheetFunction.Row(ProductID) = WorksheetFunction.Row(Range("A:A").End(xlDown)) + 1 Then Exit For
        ElseIf IsEmpty(ProductID) Then Next ProductID
        Else:
             *INSERT VARIOUS STATEMENTS TO BE PERFORMED ON DATA ROWS*
             End If
                     
    Next ProductID

The problem, of course, is that I cant put the "Next ProductID" part of the For Each loop inside the If Then conditional. What I need to do is increment the For Each loop whenever one of those special cases is reached, or in the case of the first Elseif clause (which checks to see if the row number you are on is the one after the last row with data, and exits the loop if it is).

Any help on this would be much appreciated!

What I do is set a reference to the last cell in the column (I used to say 'When loop finds a blank cell then end but of course when people start inserting lines it goes wrong very quickly. To be honest I don't really understand your code but this is what I do when looping down a column..

'Find last cell in column A by going to cell A65536 then moving up..
LastRow = Range("A65536").End(xlUp).Row

'loop all rows starting from row 2 until the end row
For x = 2 to LastRow
If cells(x,1).value <> "" then
'do some more code
'you probably realise but cells(x,1) means the cell being evaluated is row x (The current row) and column 1 (i.e Column A)
End If
Next

Maybe I've completely misunderstood but let me know if it helps/doesn't make sense

John

John,

Thanks for your response! I follow your code, and I think it would accomplish what I'm trying to do.

Question: in the line

If cells(x,1).value <> "" then

Does the <> operator stand for "does not equal"?


Also, though this does solve my problem, my question was more theoretical than practical. I realized I could set a specific looping range, and your method extends the flexibility of this method by having the end of the range determined by the last used row. But what I'm also interested in is the For Each loop in particular, because its iteration is built-in, rather than tracked by an integer of my own making.

To explain my code a bit more: I have a For Each loop that goes through every cell in the column, but the loop by itself has no way of knowing
A) to skip over the first row (the title)
B) to skip over any blank rows in between rows of data
C) to stop at the last row of data

Hence my attempt to add an IfThen statement inside the For Each loop with ElseIfs that would account for each of the 3 special cases I just listed. My problem is that I can't iterate or exit the For Each loop from within the IfThen statement.

Here's the english version of what I want to say, using the the ForEach and IfThen statements:

For Each Cell in the Column

If the row number of the cell is 1, then iterate the loop (ie go to next cell);

Elseif the row number of the cell is 1 more than the last used row, then exit the ForEach loop (ie stop the loop cuz we're done!);

Elseif the cell is blank, then iterate the loop;

Else perform the various statements I have for rows with data;

End the if statement

End the For Each loop