macro does absolute reference

[tomas]

Hey probably easy problem I am learning macros. ( started today ). Macro should for applied cells remove formulas and keep values only. In file look at sheet mixed names.

When I highlighted column B and on the ribbon pressed copy and paste , paste values,
finished recording I used this macro on column d and it worked. But when I recorded macro the other way ,column b click right mouse ,kept it hover over column C and then move back to column B, then copy as values only, stop recording. Then I tried apply on column D but macro highlighted column B and did nothing to D column. It's called macro 1 . Can someone look at it?

 

[Ajesh]

No file uploaded.
Any way you can record your macro with relative referencing

Go To View>>Macros
Activate "Use Relative References" and then record your macro. Time till you have this activated your macros will be recorded with relative references.

 

[tomas]

No file uploaded.
Any way you can record your macro with relative referencing

Go To View>>Macros
Activate "Use Relative References" and then record your macro. Time till you have this activated your macros will be recorded with relative references.

 

[tomas]

I uploaded file, so this helped what you said but if I only wanted to use macro for one cell in column the it was done for whole column so not exactly what I tried accomplish. I would just like to apply macro to whatever my celection of cells is

 

[Ajesh]

Try this:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+w
'
  With Selection
  .Copy
  .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  Application.CutCopyMode = False
  End With
End Sub

 

[Smallman]

Hi @tomas

If you are trying to hard code the cells in the active column best not to copy and paste values, best to tell Excel that you want the cells to equal their current value. A1.value = A1.value

Here is an example using your problem.

Sub Quicker()
Dim i As Long
Dim rng As Range
 
iCol = ActiveCell.Column
Set rng = Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp))
rng.Value = rng.Value
End Sub

Take care

Smallman

 

[p45cal]

I would just like to apply macro to whatever my celection of cells is

As Smallman's suggestion but tweaked to cater for the above, untested:

Sub Quicker2()
Selection.Value = Selection.Value
End Sub