How to Overwrite Named range on itself paste as only value & formatting?

[Chirag R Raval]

Dear Sirs,

I create named range by VBA & in it have some formulas, I want to overwrite this range on itself with "paste as value" so there are no formulas reside in this range. only result
& formatting remains due to daily need to mail this file without any formula but formatting required.

DEFINE 2ND RANGE HERE
  Dim rng2 As Range
  Dim my2ndrng As Range
 
    Range("E1").Select
    Selection.End(xlDown).Select
    Selection.Offset(3, -1).Select
    Selection.Resize(5, 3).Select
    Set rng2 = Selection
    ActiveWorkbook.Names.add name:="My2ndRng", RefersTo:=rng2
   
    'HOW TO ACCESS OR SELECT THIS RANGE?
    Application.Goto Reference:=ActiveWorkbook.Names("My2ndRng").name

but I can not overwrite this range with itself.

'
'BEFORE DELETE ALL NAMED RANGE ALL FORMULAS RANGE OVER WRITE WITH VALUES

with ActiveWorkbook.Range("my2ndrng").Copy

.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

End With

help will be appreciated .

Thanks

Regards,
Chirag Raval

 

[Marc L]

Hi !

For the range named as "My2ndRng" :

[My2ndRng].Formula = [My2ndRng].Value …

But creating a named range for this is very suplerfluous as
just respecting TBTO rule :​

    With Range("E1").End(xlDown).Offset(3, -1).Resize(5, 3)
        .Formula = .Value
    End With

A good code ? A no Select code !

Do you like it ? So thanks to click on bottom right Like !
​

 

[Hareesh_KLD]

Hi with the help of Marc L Tip

Sub DefineRng()

    Dim rng2 As Range
 
    Set rng2 = Range("E1").End(xlDown).Offset(3, -1).Resize(5, 3)
   
    rng2.Formula = rng2.Value
   
End Sub

 

[Marc L]

Hareesh, do not forget to release any object variable
before the procedure ends !
A must read : Nothing in VBA inner help …​

 

[Hareesh_KLD]

Thanks for the tip

 

[Chirag R Raval]

Dear Sirs @MarcL & @ Hardest,

Thank you very much for your kind help.
I really feel sorry for create named range based on selection.

I really learned from here, though target range is dynamic,
it can be set without select & thanks for that.

We can Reach & Access or Reference to any cell on sheet
And base on that cell, with the help of Directions (Left, Right, Up, Down)
Use .End , use offset, Use of Resize (Resize become core if you have
Base & depend on just 1 cell & want contiguous cell"s range)
that's create basic funda of how to Access/Reference/Pointing
Range on sheet in our mind That's Great.

Though , for requirement of dynamic range ,
We can depend on filled cells , formatted cell, Special cell, ect.
To jump/ Acess/ Reference somewhere on sheet.

Though temp set this range in variable (that I already settled before naming it)
& Access / Pointing/Reference it,
One point I noted that both of you ignore Named range aspect.

How to direct Access/Reference/Pointing "Named" range?
& Overwrite on itself with just values &
Formatting remains?

Sorry but What it TBTO rule?

If you have curiosity like child,
Excitement here for learning is just amazing.

Because little matter, explore new world
Of learning about lots of things ..like ride on roller coaster.

Which help you in you life that how to do things with many aspects.

Learn for For life time...With some more... just great.

Regards,

Chirag Raval

 

[Marc L]

My thought was a named range is not necessary
just to replace formulas by values.
Of course a named range can be helpful for a common use …

How to direct Access/Reference/Pointing "Named" range?
& Overwrite on itself with just values & Formatting remains?

Like I yet wrote in my previous post with a direct way :

For the range named as "My2ndRng" :

[My2ndRng].Formula = [My2ndRng].Value

Or with the indirect way :​

    With [My2ndRng]
        .Copy
        .PasteSpecial xlPasteValues
    End With

Like you can do manually by activating Macro Recorder …

What it TBTO rule?

Think, But Think Object !

Just the direct way pointing an object …
A variable just to use it once ? No way !
(Except in case of a special need …)
​

 

[Hareesh_KLD]

Sub DefineRng()

    Dim rng2 As Range

    Set rng2 = Range("E1").End(xlDown).Offset(3, -1).Resize(5, 3)
 
    ThisWorkbook.Names.Add Name:="My2ndRng", RefersTo:=rng2
   
    Range("My2ndRng").Formula = Range("My2ndRng").Value
     
End Sub

 

[Chirag R Raval]

Dear Sirs @Marc L & @Hareesh_KLD,

Both's Code are Great..Thak You.

for post no 7 of Sir Marc L, right there are no need of named range...due to already set this range & we can directly refer it.

Regards,
Chirag Raval