For Each loop issue

[VeryForgetful]

Hi all,

I have an issue with a for each loop where I want to limit a set range to processing 100 rows on each run.

So the first interaction would be range L2 to L101 then L102 to L203 and so on. The last one would more than likely have less than 100 rows so that would only need to loop as far as the last row.

Any ideas please?

 

[Michael M]

Can you post what you already have ???

 

[VeryForgetful]

Thanks

[COLOR=#454545][FONT=&quot][FONT=&quot]Sub Example()[/FONT][/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot][/FONT]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot]Dim cel As Range, rng As Range, lr as long[/FONT][/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot]Dim MyStr as String[/FONT][/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot][/FONT]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot]lr = Cells(Rows.Count, “L”).End(xlUp).row[/FONT][/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot]Set rng = Range(“L2:L” & lr)[/FONT][/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot][/FONT]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot]For Each cel In rng[/FONT][/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot]  MyStr = MyStr & cel.Value & “,”[/FONT][/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot]Next cel[/FONT][/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot][/FONT]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot][FONT=&quot]End Sub[/FONT][/FONT][/COLOR]

 

[Norie]

Not sure what you are trying to do but perhaps you could use something like this.

Sub Example()
Dim cel As Range, rng As Range, lr as long
Dim MyStr as String
Dim cnt As Long

    lr = Cells(Rows.Count, “L”).End(xlUp).row
    Set rng = Range(“L2:L” & lr)

    For Each cel In rng
        MyStr = MyStr & cel.Value & “,”
        cnt = cnt + 1
        If cnt = 100 Then
            Debug.Print MyStr
            MyStr = ""
            cnt = cnt + 1
        End If
    Next cel

End Sub