Value2 - formula >>> value, addition to VBA code

[heathball]

For what it's worth, the pathway to utilizing 'value2' as it pertains to removing a formula, then leaving only the resulting value, is some type of Freemason/Skull and Bones secret. With respect to the mentioned application of the term, describing how it could be applied etc etc, I can identify exactly zero references, on the entire global internet system. I don't believe it exists. Though, it probably could be found in a book somewhere.

It was alluded to, on a South African website, in 2013, in the middle of a different subject, that this is worth investigating, with efficiency benefits when compared to a copy/paste........which is something a person like me would normally do. It's not a talking point.

I have got as far as the code below would suggest, which is not very far. That includes the point about how i stole the entire diddy from this very website. I added the crucial part of the last line. The code works very well. But life goes on, and it's time to expand the idea, and spruce things a bit.

I want to keep the formulas in cells 2-31 ( i use this code in all parts of different sheets, so there are no exact cell references.)
I am looking to select a range from cell 32-down, in the same column or columns, inclusive - to the bottom of the conspiricy..'eh...i mean "sheet"
eg B32:Z32,B??????:Z?????? ?=last row used in column A
I would prefer to keep 'selection' and not lock the code into cell 32, if possible. So the new code starts from cell32, because it is the "selection".

In this range, formulas are to be removed, leaving just the calculated value. ...Sort of like a copy/paste, really...but with a certain mysterious allure added.

I attempted to reapply this ******"Range("A" & Rows.Count).End(xlUp).EntireRow))"******- it works about as well as i do operating a kitchen stove. I would steer clear of it.

Any help in demystifying this will, at the very least, register an internet searchable reference to this challenge, for perhaps the first time ever. That's why i spent such a long time crafting the thread title.

Sub AUTOFILL_PASTE_lastrow_ColA()


Selection.AutoFill Range(Selection, Intersect(Selection.EntireColumn, Range("A" & Rows.Count).End(xlUp).EntireRow))
Selection.EntireColumn.Select
If TypeName(Selection) = "Range" Then Selection.Calculate
Selection(32).Select


    

End Sub

 

[heathball]

you nailed it! Thanks Alex.

I did confuse you. I confused myself first, and perhaps this entire page could be part of a Mr Excel training blog on how not to mangle a post

Perhaps not bad enough to change my log-on profile, but I will have to lay low for a few weeks after this performance.

But we got there in the end, thanks everybody!

 

[Alex Blakenburg]

Haha not at all. We are happy to have been able to help and appreciate that you have acknowledged that there were multiple contributors