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

 

[6StringJazzer]

Although I found your post very entertaining and wry, I am having trouble divining what your actual question is. It seems to be buried in there somewhere.

You appear to want to convert a range of cells from formulas to their values. Do you want to convert B32:Zn from formulas to values where n is the last row of data in column A?

The code you posted seems to bear no relation to what you want to do. Please try the code below.

You will note that references to ranges are not qualified with the worksheet object, therefore allowed to default. This sub is intended to be used:
- In a standard module (e.g., Module1) where the worksheet to be modified is the active sheet
or
- In the module for the worksheet to be modified

Public Sub ConvertToValues()

   Dim LastRowA As Long
   
   ' Determine the last row of column A that has data
   LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
   
   ' Convert formulas in columns B:Z to values starting in row 32 to LastRowA
   With Range(Cells(32, "B"), Cells(LastRowA, "Z"))
      .Value2 = .Value2
   End With

End Sub

 

[Alex Blakenburg]

I am also a little unclear on what you are trying to do but combining what Jeff (@6StringJazzer) had and your code might look something like this:

Sub ConvertToValues()

    Dim rng As Range
    Dim rArea As Range
    Dim LastRowA As Long
    
    Set rng = Selection
    LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
    
    Set rng = Intersect(Rows(rng.Row & ":" & LastRowA), rng.EntireColumn)
    For Each rArea In rng.Areas
       rArea.Value2 = rArea.Value2
    Next rArea

End Sub

 

[MARK858]

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

Why do you imply this doesn't work? do some of your formulas return a "" and so the cells aren't actually blank (that code will find the last cell with any data including formulas returning "")?

 

[heathball]

The code does work. i will try to be clearer in my explanation.


the code works in this way.......
After I choose a cell containing a formula, (or choose cells (that must be in the same row) containing formulas)
i activate the code. . its a copy-paste-calculate code, for the selection only
Then autofill occurs -
eg. If i have selected 4 cells, B31:E31
In simple terms, it copies the cells/formulas in the range B31:E31, and pastes those cells/formulas directly below, into all cells, down to the row that matches the last cell used in Column A
Then it selects the entire range of all active columns - then calculates. (i have manual calculation set, because of big files)
the last line is returning to where i want to begin the next additional component.

Its doing what i want.

When i try to alter it, i am not able to get what i want, possibly because of "intersect" (i assume intersect performs the role of allowing me to select cells in more than 1 column, which is essential - because i have similar codes that do not allow this task to occur in more than 1 row)

So what i am looking for is an addition to this code.
Because of how the code works, what i want may not be possible

I am looking for "value2" to be applied in all cells from Row 32-down to the row that matches the last cell used in Column A
There are formulas in rows 2-31, which i want to retain.

this is the same code, with the last line removed, plus my added code.

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

(i hereby inject the additional code, in my code language, meant for understanding concept only)
in.active.columns         ---the columns containing my original selections - this may not be required, if they are already selected, (it appears they are)
Select.cells.Row(32)Andbelow.Range("A" & Rows.Count).End(xlUp).EntireRow))       -----selects cells in row 32 and below -only in the selected columns. cells in Rows 1-31 are not selected
Selection.Value2       ------this converts the formulas to values - (so my big files don't turn into slow moving Estonian farm tractors from the 1930's)
Selection (2)       --------so i know where the active cell is

 

[Alex Blakenburg]

@heathball, so did you try my code in post #3

 

[heathball]

@heathball, so did you try my code in post #3

Thanks Alex,
i have tried to apply your code in #3 in lots of ways
what that does, is convert ONLY the selected row into values.

i don't see an "autofill" part of that code (i tried to read what you wrote carefully)
but maybe its there in the code and i cannot see it, but the autofill part is not happening.

it seems to be an attempt at a complete replacement of the original code - i cannot achieve that so far

But if there is something you think i could be missing, please let me know.

 

[heathball]

Thanks Alex,
i have tried to apply your code in #3 in lots of ways
what that does, is convert ONLY the selected row into values.

i don't see an "autofill" part of that code (i tried to read what you wrote carefully)
but maybe its there in the code and i cannot see it, but the autofill part is not happening.

it seems to be an attempt at a complete replacement of the original code - i cannot achieve that so far

But if there is something you think i could be missing, please let me know.

@Alex Blakenburg​

I am a little confused.
The code at the top is not doing what i said it does. It looks like i have made an error somewhere.
Apologies for wasting your time.
I am reviewing and will post again later.

 

[heathball]

ok, Visual Basic ceased to work for a short period, its all good now, and back to normal.

The code at the top does work perfectly, and is the correct code -starts with my selection, autofill, apply formulas to End(xlUp), and calculate,

@Alex Blakenburg - back to your code #3
it works very quickly to convert the formulas to values, so its very interesting, and i will certainly use it. thank you.

The goal now is to join these 2 codes into 1 code

my code below ends up with the active columns selected.
I need the code to select the cells in row32, of the columns selected,
then find the appropriate segway, if needed, to apply the new "value2" code, joining them into one code.

eg. columns D:G are selected
I need the range D32:G32 selected.

But without the column references in the code, as i want to apply to many different columns, quickly.

Is this possible, or is it too ambitious because it would require a column reference to work?

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


------------------------------------


Sub ConvertToValues()
    Dim rng As Range
    Dim rArea As Range
    Dim LastRowA As Long
       Set rng = Selection
    LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
      Set rng = Intersect(Rows(rng.Row & ":" & LastRowA), rng.EntireColumn)
    For Each rArea In rng.Areas
       rArea.Value2 = rArea.Value2
    Next rArea
End Sub

 

[Alex Blakenburg]

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

I need the code to select the cells in row32, of the columns selected,

I am confused. In the initial post it is saying you don't want row 32 hard coded but in the last post I am reading it as that you do want it to be hard coded.

Replace both pieces of code with this and let me know what it needs to be doing differently to meet your requirements.

Sub AutoFillConvertToValues()

    Dim rng As Range
    Dim rArea As Range
    Dim LastRowA As Long
    
    Set rng = Selection
    LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
    
    Set rng = Intersect(Rows(rng.Row & ":" & LastRowA), rng.EntireColumn)
    For Each rArea In rng.Areas
        rArea.Rows(1).AutoFill rArea, Type:=xlFillCopy
        rArea.Value2 = rArea.Value2
    Next rArea

End Sub