Remove cell from range

[Michael]

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

 

[Don Guillett Excel MVP]

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

 

[Michael]

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.