Hi all

I have a named range ("List") that is a non-contiguous range, eg A1,
A3, A5

If the last cell has a certain value I wish to redefine the range to
exclude that cell (ie, A1, A3)

What's the best way to do this?

Regards

Paul Martin
Melbourne, Australia

Hi all

This may not be the best way, but it works, because I am able to delete
the unwanted value:

Dim rngList As Range

Set rngList = Range("List").SpecialCells(xlCellTypeConstants)

With rngList
If .Areas(.Areas.Count).Value = "SomeValue" Then
.Areas(.Areas.Count).ClearContents
End If
End With

Set rngList = Range("List").SpecialCells(xlCellTypeConstants)


Paul Martin
Melbourne, Australia

On 9/1 I posted a question as to remove a subrange from a range ( basically
to un-do an UNION). Tom Ogilvy posted the following macro:

Function Disunion(rng As Range, NewStuff As Range) As Range
Dim rng1 As Range, cell As Range
For Each cell In rng
If Intersect(cell, NewStuff) Is Nothing Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(cell, rng1)
End If
End If
Next
Set Disunion = rng1
End Function

This re-builds the range cell-by-cell, excluding the un-wanted part.
Perhaps this can help you.
--
Gary''s Student


"Paul Martin" wrote:

Hi all

This may not be the best way, but it works, because I am able to delete
the unwanted value:

Dim rngList As Range

Set rngList = Range("List").SpecialCells(xlCellTypeConstants)

With rngList
If .Areas(.Areas.Count).Value = "SomeValue" Then
.Areas(.Areas.Count).ClearContents
End If
End With

Set rngList = Range("List").SpecialCells(xlCellTypeConstants)


Paul Martin
Melbourne, Australia

I've seen Tom's code previously, and it would work in this case, but not
as efficiently as how I am doing it now. Thanks all the same.

Regards

Paul Martin
Melbourne, Australia

*** Sent via Developersdex http://www.developersdex.com ***