Add non-contiguous cells to range?

[Ed]

A worksheet has one column of invoice numbers. The invoice number is
repeated in contiguous rows for each item in listed on that invoice.
The column to the left has text in some cells. Right now I am
grabbing the invoice number from another sheet, using Find to set an
initial range to the first cell in the column with that number, and
then using the loop code below to extend the range to include all the
cells with that same number.

Do
If rngWork.Offset(1, 0).Text = strInvNo Then
Set rngWork = Union(rngWork, rngWork.Offset(1, 0))
Else
Exit Do
End If
Loop

What I would like to do, but can't figure out how, is to check the
cell to the left for text: if text is present, do NOT add the cell to
the range. Something like:

Do
If rngWork.Offset(1, 0).Text = strInvNo And _
rngWork.Offsset(1,1).Text = "" Then
ADD rngWork.Offset(1, 0) TO RNGWORK
Else
Exit Do
End If
Loop

Union and Intersect I know. But how to I add a non-contiguous cell to
a range?

Ed

 

[John Green]

Ed,

Union can be used to create a non-contiguous range, but you will need to
change the way you track the data you are testing. Assuming that rngWork
starts out as a single starting cell (and you mean the cell to the right has
the text), you could use something like the following:

Set rngStart = rngWork

lRow = 1

Do While rngStart.Text = rngStart.Offset(lRow, 0).Text

If rngStart.Offset(lRow, 1).Text = "" Then
Set rngWork = Union(rngWork, rngStart.Offset(lRow, 0))
End If

lRow = lRow + 1

Loop

John Green

 

[Ed]

John: Thanks so much! It worked great! Every time I tried Union
before, I would wind up with everything. This, though, did exclude
the cells with text.

Thanks for the boost.
Ed