To be sure looping through all the rows

[nkamp]

Hello,

I looping through the rows in excel sheet by a While loop like:


While (Trim(Worksheets("openstaande orders").Range("A" & iRijTel)) <> "" _
Or Trim(CStr(Worksheets("openstaande orders").Range("J" & iRijTel))) <> "" _
Or Trim(CStr(Worksheets("openstaande orders").Range("K" & iRijTel))) <> "")
If (Trim(CStr(Worksheets("openstaande orders").Range("A" & iRijTel))) = Trim(stOpdrNr)) Then
FindOpdrnr = iRijTel
Exit Function
End If

iRijTel = iRijTel + 1
Wend


I check if some coloms are empty. And if so than the looping stops. But is there a possibility that you have a object or collection of the rows, so that is sure loop through all rows and that I'm independit of the user filling in the columns correctly.

Something like:

For each rw in Rowcollection

Next rw


Thanks in advance,

Nico

[Bob Phillips]

Dim RowCollection As Range
Dim mpRow As Range

Set RowCollection = Rows("12:16")
For Each mpRow In RowCollection.Rows

MsgBox mpRow.Cells(1, 1).Address
Next mpRow

[nkamp]

Xld,

Thanks, but one thing I was forgotton to mention is that I don't know how many rows there are. Maybe today 16 but tomorrow 54 or less. Oke, never more then 500, so I can say 7:500. But I was asking myself is there method or object which defines right the number of rows where is something filled in.

Nico

[Bob Phillips]

Then you need a key column that will have a value for every row to be considered. Is ther one of these?