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

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

--
Gary''s Student - gsnu200735


"Ray" wrote:

> 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
>
>

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



In article <(E-Mail Removed)>,
Ray <(E-Mail Removed)> wrote:

> 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

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?

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

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

NOW it works ...!

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.

In article <(E-Mail Removed)>,
Ray <(E-Mail Removed)> wrote:

> 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?

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