Clear UsedRange except specific cells?

[Ray]

I have some code which currently loops through my worksheets (30+) and
'values out' the UsedRange (approx A1:Z50). I'd like to use a
hyperlink in cells A4:C4 (merged cells) to let users navigate back to
a Summary page. However, my code wipes out that formula ...

If there a way to exclude these cells (A4:C4) from the code to value
out the UsedRange?

rgds, ray

 

[Gary''s Student]

Sub clearexcept()
Set rp = Range("A4:C4")
For Each r In ActiveSheet.UsedRange
If Intersect(r, rp) Is Nothing Then
r.Clear
End If
Next
End Sub

 

[JE McGimpsey]

One way:

Dim ws As Worksheet
Dim rToClear As Range
Dim rMask As Range
For Each ws In ThisWorkbook.Worksheets
With ws
Set rMask = Union(.Rows(5).Resize(.Rows.Count - 4), _
.Columns(4).Resize(4, .Columns.Count - 3))
Set rToClear = Intersect(.UsedRange, rMask)
If Not rToClear Is Nothing Then rToClear.Clear
End With
Next ws

 

[Ray]

Hi G.S. -

I tried to implement your suggestion, but the code errored out on the
Intersect line .... here is the error and the code as I'm using it:

RunTime 1004: Method 'Intersect' of Object '_Global' failed


Sub ValueOutDSRs()
Dim sh As Worksheet

Application.Calculation = xlCalculationManual
For Each sh In Sheets(Array("DSR - 152",<a bunch of sheets here>,"DSR
- 250", "DSR - 921"))

sh.Activate
Set rp = Range("A4:C4")
For Each r In ActiveSheet.UsedRange
If Intersect(r, rp) Is Nothing Then
r.Formula = r.Value
End If
Next r

Next sh
Application.Calculation = xlCalculationxlAutomatic

End Sub

Any thoughts?

 

[Ray]

A-HA! Figured it out ... the 'set rp...' line should read like this:

Set rp = ActiveSheet.Range("A4:C4")

NOW it works ...!

 

[JE McGimpsey]

That's rather inefficient. Try:

Dim ws As Worksheet
Dim rConvert As Range
Dim rMask As Range
For Each ws In Worksheets(Array(...))
With ws
Set rMask = Union(.Rows(5).Resize(.Rows.Count - 4), _
.Columns(4).Resize(4, .Columns.Count - 3))
Set rConvert = Intersect(.UsedRange, rMask)
If Not rConvert Is Nothing Then _
rConvert.Value = rConvert.Value
End With
Next ws

which converts formulas to values in all the cells in the worksheet
(except those masked) in one step, rather than looping through each cell.

 

[Ray]

Holy Cow! JEM, you are most certainly right ... your code was MUCH
faster! A very un-scientific test showed a significant difference,
~10secs vs 145secs! I was originally a bit 'scared' of your code, as
I was having a hard time making sense of it .... I prefer to use code
that I can somewhat decipher.

Thanks very much for your input and persistence in getting me to try
it!

Ray