Intersect or Union?
 

Public Sub Test()
With antiunion(Sheets(1).UsedRange,range("I44:N55"))
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
End With
End Sub

'-----------------------------------------------------------------
Function AntiUnion(SetRange As Range, UsedRange As Range) As Range
'-----------------------------------------------------------------
Dim saveSet
saveSet = SetRange.Formula
SetRange.ClearContents
UsedRange = 0
Set AntiUnion = SetRange.SpecialCells(xlCellTypeBlanks)
SetRange = saveSet
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Nigel" wrote in message
...
I have an application that copies the used range on a worksheet and then
pastes values, to remove formula.

I need to deselect a smaller range within the used range before I paste
values, so for the smaller range the formula are retained.

The following replaces all formula, but if I wanted to exclude a smaller
range say ("I44:N55") how could I do this?

With Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
End With



--

Regards,
Nigel