Can I use array to simplify this code.

Sorry for the mess in the previous posting.

Can I use array to simplify this code.
Appreciate your help. Thanks

sub test()
Dim rngJ As Range
Dim rngK As Range
Dim rngL As Range
..
..
..
For Each Cell In rngK
---code---
next

For Each Cell In rngL
---code--- (same as above)
next

For Each Cell In rngM
---code--- (same as above)
next
..
..
..
end sub

------------------------

Notes: rngK, rngL and rngM located in different sheets.

Hi,
I'm not sure about an array but you could use a function for the
----code---(same as above) parts of your loop...

Function MyFunction(rngCell as Range) as Range
----code----
End Function

This would simplify your code and reduce duplicity.
Hth,
OJ

Hi broogle,

Try something like...

Sub test()

Dim rngArr(3) As Variant
Dim rng As Variant

' populate the array
Set rngArr(1) = Worksheets(1).Range("A1:A5")
Set rngArr(3) = Worksheets(1).Range("C1:C5")

For Each rng In rngArr
' because we have declared a variant array, we can test for empty members
If Not IsEmpty(rng) Then
' do something... (place your own codew here)
MsgBox rng.Address
End If
Next

End Sub

Ed Ferrero

> Can I use array to simplify this code.
> Appreciate your help. Thanks
>
> sub test()
> Dim rngJ As Range
> Dim rngK As Range
> Dim rngL As Range
> .
> .
> .
> For Each Cell In rngK
> ---code---
> next
>
> For Each Cell In rngL
> ---code--- (same as above)
> next
>
> For Each Cell In rngM
> ---code--- (same as above)
> next
> .
> .
> .
> end sub
>
> ------------------------
>
> Notes: rngK, rngL and rngM located in different sheets.
>

You received two good answers to your original post.

--
Regards,
Tom Ogilvy

"broogle" <[email protected]> wrote in message
news:[email protected]...
> Sorry for the mess in the previous posting.
>
> Can I use array to simplify this code.
> Appreciate your help. Thanks
>
> sub test()
> Dim rngJ As Range
> Dim rngK As Range
> Dim rngL As Range
> .
> .
> .
> For Each Cell In rngK
> ---code---
> next
>
> For Each Cell In rngL
> ---code--- (same as above)
> next
>
> For Each Cell In rngM
> ---code--- (same as above)
> next
> .
> .
> .
> end sub
>
> ------------------------
>
> Notes: rngK, rngL and rngM located in different sheets.
>