I am trying to turn all my formulas into values for the last used column in my sheet. (Last column changes monthly) This is my code so far:
Sub turnvalues2() Set rng = Range(4, Columns.Count).End(xlToLeft).Column - 1 rng.Value = rng.Value End Sub 
Add a comment |
2 Answers
Range(4, Columns.Count).End(xlToLeft).Column - 1 returns a number not a range. You want the Columns() at that number.
Sub turnvalues2() Set rng = Columns(Cells(4, Columns.Count).End(xlToLeft).Column - 1) rng.Value = rng.Value End Sub Also the - 1 moves it to the second to last column used, You may want to remove that to get the last column used.
3 Comments
cthallofamer
when I used the code, I got "Runtime error 1004: Method 'range' of Object_'Global' failed"
cthallofamer
also, I did it without the -1 and still got the same error
tigeravatar
Change
Range to Cells and that will fix the issueAlternate version using Range.Find to get last populated column:
Sub tgr() Dim ws As Worksheet Dim rFound As Range Set ws = ActiveWorkbook.ActiveSheet Set rFound = ws.Cells.Find("*", ws.Range("A1"), xlFormulas, , xlByColumns, xlPrevious) If Not rFound Is Nothing Then rFound.EntireColumn.Value = rFound.EntireColumn.Value End Sub