Intersect or Union?

[Nigel]

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

 

[Bob Phillips]

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)

 

[Joerg Mochikun]

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

 

[Bob Phillips]

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

 

[Bob Phillips]

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