Dim rng as Range
Dim rng1 as Range
Dim rngF as Range
Dim range2_for_the_OnlyData as Range
set rngF = Range(cells(7,"F"),Cells(rows.count,"F").End(xlup) )
On Error Resume Next
set rng = _
rngF.SpecialCells(xlCellTypeConstants).EntireRow
set rng1 = _
rngF.SpecialCells(xlCellTypeFormulas).EntireRow
On Error goto 0
if not rng is nothing and not rng1 is nothing then
Set range2_for_the_OnlyData = Union(rng,rng1)
Elseif not rng is nothing then
set range2_for_the_OnlyData = rng
elseif not rng1 is nothing then
set range2_for_the_OnlyData = rng1
End if
if not range2_for_the_OnlyData is nothing then
Set range2_for_the_OnlyData = Intersect(range2_for_the_OnlyData,
Range("i:L"))
range2_for_the_OnlyData.Copy

Sheets("OnlyData").Range("j25").PasteSpecial xlPasteValues
End If

--
Regards,
Tom Ogilvy


lothario wrote in message
...
Thanks Tom. This worked.
One quick follow up question:

How do I change your code so that this (conditional) search
and copy begins in column F after row 7? Rows 1 to 6 should
be completely ignored. (They contain titles.)

Thanks.


---
Message posted from http://www.ExcelForum.com/