Remove cell from range
 

I am building a range of cells from a selection that meet certian
criteria. I am using union to build up the range. First problem is
that I cant union a range with itself so on the first loop through it
fails. I get around this by always added cell "a1" to the range but
then once I am done I need to remove cell "a1" from the new range. Is
there a simple strait foward way to drop one particular cell from a
range. I dont want to convert to a long string of cell values because
I think at some point I will run up against string lenght limits.

See below. Any help would be appreciated. Thanks.


Set rng = Selection
'include range A1 in newRng to that union will work on first
iteration
Set newRng = Range("a1")

For Each c In rng
If IsNumeric(c.Formula) Then
If CDbl(c.Value) = CDbl(c.Formula) Then
Set newRng = Union(newRng, c)
End If
End If
Next c

Remove cell from range
 

On Oct 19, 7:54*am, Michael wrote:
I am building a range of cells from a selection that meet certian
criteria. I am using union to build up the range. First problem is
that I cant union a range with itself so on the first loop through it
fails. I get around this by always added cell "a1" to the range but
then once I am done I need to remove cell "a1" from the new range. Is
there a simple strait foward way to drop one particular cell from a
range. I dont want to convert to a long string of cell values because
I think at some point I will run up against string lenght limits.

See below. Any help would be appreciated. Thanks.

Set rng = Selection
*'include range A1 in newRng to that union will work on first
iteration
* *Set newRng = Range("a1")

* * For Each c In rng
* * * * If IsNumeric(c.Formula) Then
* * * * * * If CDbl(c.Value) = CDbl(c.Formula) Then
* * * * * * * * Set newRng = Union(newRng, c)
* * * * * * End If
* * * * End If
* * Next c
Modify this idea to suit your needs
Sub Hide_me()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & LastRow)
For Each c In MyRange
If IsEmpty(c) Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.EntireRow.Hidden = True
End If
End Sub

Remove cell from range
 

On Oct 19, 9:16*am, Don Guillett Excel MVP
wrote:
On Oct 19, 7:54*am, Michael wrote:



I am building a range of cells from a selection that meet certian
criteria. I am using union to build up the range. First problem is
that I cant union a range with itself so on the first loop through it
fails. I get around this by always added cell "a1" to the range but
then once I am done I need to remove cell "a1" from the new range. Is
there a simple strait foward way to drop one particular cell from a
range. I dont want to convert to a long string of cell values because
I think at some point I will run up against string lenght limits.

See below. Any help would be appreciated. Thanks.

Set rng = Selection
*'include range A1 in newRng to that union will work on first
iteration
* *Set newRng = Range("a1")

* * For Each c In rng
* * * * If IsNumeric(c.Formula) Then
* * * * * * If CDbl(c.Value) = CDbl(c.Formula) Then
* * * * * * * * Set newRng = Union(newRng, c)
* * * * * * End If
* * * * End If
* * Next c

Modify this idea to suit your needs
Sub Hide_me()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & LastRow)
For Each c In MyRange
If IsEmpty(c) Then
* * * * If MyRange1 Is Nothing Then
* * * * * * Set MyRange1 = c.EntireRow
* * * * Else
* * * * * * Set MyRange1 = Union(MyRange1, c.EntireRow)
* * * * End If
* * End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.EntireRow.Hidden = True
End If
End Sub- Hide quoted text -

- Show quoted text -

I am not quite sure what this does and could not figure it out from
running on a alomst blank sheet. But it looks like you are doing the
same thing as I was by adding cell A1 to make the union work - but you
added the whole row (c.EntireRow). What I dont see is how you then
drop those cells since yiou dont need them and in fact dont want them
as part of you output range.