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