Macro to change column contents to 'values'

I pretty much used the record feature to do this macro. This macro just copies and pastes contents as 'values' in that column. When you record a macro, excel will use the hard cell/range address (A1:A5 for example). So we need to step into the code to change it to active cell.

Sub pastecolumntovalues()
ActiveCell.Select
ActiveSheet.range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False
ActiveSheet.PasteApplication.CutCopyMode = False
End Sub

Macro to change column values in a cell to absolute values

This macro is very similar to the macro that trims column values. Instead of using the trim function, I have used the ABS function. You can also change this code to work on row values, as demonstrated by my previous example.

Sub changecoltoabs()
Dim rowcnt As Long
ActiveSheet.range(ActiveCell, ActiveCell.End(xlDown)).Select
rowcnt = Selection.Rows.Count
For i = 1 To rowcnt
ActiveCell.Cells(i).Value = Abs(ActiveCell.Cells(i).Value)
Next i
End Sub

Macro to change sign of active cell row values

This macro is almost the same as the macro to change sign of active cell column values. I have just changed the row codes to column codes, and vice versa. The previous macro would select active cells from top to bottom, whereas this will select cells to the right.

Sub Changerowtoneg()
Dim colcnt As Long
ActiveSheet.range(ActiveCell, ActiveCell.End(xlToRight)).Select
colcnt = Selection.Columns.Count
For i = 1 To colcnt
ActiveCell.Cells(1, i).FormulaR1C1 = ActiveCell.Cells(1, i) * (-1)
Next i
End Sub

Inserting blank rows between cells

This macro can be used to insert blank rows between continuous column values. This was a little tricky to program, but it worked. Like the previous macros, select the first cell and run the macro. Here is the code below.

Sub Insertblankrows()
Dim rowcnt As Long
ActiveSheet.Range(ActiveCell, ActiveCell.End(xlDown)).Select
rowcnt = (Selection.Rows.Count) * 2
For i = 1 To rowcnt
ActiveCell.Cells(2).Select Selection.Rows(i).EntireRow.Insert
Next i
End Sub

Note: If you want to just insert cells (not rows), then change code to "Insert" instead of "Entirerow.Insert" on line 6.

Macro to trim cell values in a column

This macro is similar to the previous one. Instead of changing the sign, this will trim the cell values in that column.

Sub Trimcol()
Dim rowcnt As Long
ActiveSheet.range(ActiveCell, ActiveCell.End(xlDown)).Select
rowcnt = Selection.Rows.Count
For i = 1 To rowcnt
ActiveCell.Cells(i).FormulaR1C1 = Application.Trim(ActiveCell.Cells(i).Value)
Next i
End Sub

Macro to change sign of active cell column values

This macro can be used to change sign of column values. Before running this macro, select the first cell of the column. And when you run it, the macro will change the sign of the values in that range. (Macro will stop at a blank row)

For example, if you had a column with values
1
2
-1
2,

the macro will change the column values to

-1
-2
1
-2

The code for this macro is

Sub Changecoltoneg()
Dim rowcnt As Long
ActiveSheet.range(ActiveCell, ActiveCell.End(xlDown)).Select
rowcnt = Selection.Rows.Count
For i = 1 To rowcnt
ActiveCell.Cells(i).FormulaR1C1 = ActiveCell.Cells(i) * (-1)
Next i
End Sub

To begin with

I am learning to code some macros to make our excel time more efficient. These macros have gone through some initial testing on my side, and seem to work. If you do find any issues/bugs, please let me know.

I have developed these codes primarily by trial and error, and am NOT a trained VBA programmer. I have also used the macro record feature for some subtasks .

To use these codes, you can copy them on your Visual Basic Editor. Alternatively you can use the record macro feature to create a placeholder, and overwrite the default code with the new code. As you can see, these codes can be tweaked easily to perform similar tasks. However, remember that macros cannot be undone (unless you close the worksheet w/o saving), and you cannot view the formula on the cells by using F2.

Please contact me if you have any questions regarding using these codes in your excel workbooks. You can also assign shortcut keys to your macros by going to Tools-Macro-Macros (select your macro)-Options. And make sure that you dont overwrite standard shortcut keys like Ctrl C.

Happy excel time !