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
On Mar 26, 5:56 pm, "John Green" <greenj@nospam> wrote:
> 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" <prof_ofw...@yahoo.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> >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- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
