For each Cell in More than one range. Possible

[ajmckenna]

I want the code to look in multiple ranges, Instead of recreating the code for each range


Example

For each Cell in ..."Myrange1" and Myrange2" and"Myrange3" etc

If Cell <>"" then

...code

End if

next

End sub

Any ideas? Thanks!

AJ

 

[Barrie Davidson]

Dim CheckRange As Range

Set CheckRange = Union(Range("MyRange1"), Range("MyRange2"), Range("MyRange3"))
For Each C In CheckRange
    If Cell <> "" Then
        'your code
    End If
Next

Should do the trick.

Regards,

 

[TommyGun]

Create a new range using the Union of your individual ranges...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ForEachInUnion()
    <SPAN style="color:#00007F">Dim</SPAN> unionRange <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#00007F">Set</SPAN> unionRange = Application.Union(Range("A1:B10"), Range("E1:F10"), Range("H1:I10"))
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> unionRange.Cells
        <SPAN style="color:#00007F">If</SPAN> c <> vbNullString <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#007F00">' code here</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
    
    <SPAN style="color:#00007F">Set</SPAN> unionRange = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[DRJ]

Or

For Each cell In Range("A1:D12,G3:J16,A18:C30")

 

[ajmckenna]

Thanks Union should do the trick!

Did not know it existed.

Cheers!