Maybe:

For Each cell In Sheets(1).UsedRange
If Application.Intersect(cell, Range("I44:N55")) Is Nothing Then
cell.Copy
cell.PasteSpecial xlPasteValues
End If
Next cell

Your idea to deselect an area from the used range will only work if the area
to be excluded from copy/paste is at the edged of the used range because
copy and paste ranges have to be rectangular. Above code will work with any
range.

Cheers,

Joerg Mochikun


"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

Good point about multiple areas, I didn't even test that far, just that it
got the correct range(s)

--
__________________________________
HTH

Bob

"Joerg Mochikun" wrote in message
...
Maybe:

For Each cell In Sheets(1).UsedRange
If Application.Intersect(cell, Range("I44:N55")) Is Nothing Then
cell.Copy
cell.PasteSpecial xlPasteValues
End If
Next cell

Your idea to deselect an area from the used range will only work if the
area to be excluded from copy/paste is at the edged of the used range
because copy and paste ranges have to be rectangular. Above code will work
with any range.

Cheers,

Joerg Mochikun


"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

You can use the technique o iterate the ares though rather than all cells

Public Sub Test2()
Dim mpArea As Range
For Each mpArea In AntiUnion(Sheets(1).UsedRange, Range("I44:N55")).Areas
mpArea.Cells.Copy
mpArea.Cells.PasteSpecial xlPasteValues
Next mpArea
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

"Bob Phillips" wrote in message
...
Good point about multiple areas, I didn't even test that far, just that it
got the correct range(s)

--
__________________________________
HTH

Bob

"Joerg Mochikun" wrote in message
...
Maybe:

For Each cell In Sheets(1).UsedRange
If Application.Intersect(cell, Range("I44:N55")) Is Nothing Then
cell.Copy
cell.PasteSpecial xlPasteValues
End If
Next cell

Your idea to deselect an area from the used range will only work if the
area to be excluded from copy/paste is at the edged of the used range
because copy and paste ranges have to be rectangular. Above code will
work with any range.

Cheers,

Joerg Mochikun


"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