Insert your macro code into a module, start with Sub Auto_Open(). Enter whatever commands you wish to have executed on opening the file and end with End Sub.
Copy all of the code below. Paste it into your workbook’s Visual Basic editor, in a Module. Replace “Msgbox “Hello” ” with whatever macro commands you wish to have run whenever your file is opened.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Sub Auto_Open()
'
'MACROS BY EXCELZOOM.COM
'
Msgbox "Hello"
End Sub
Use the following if you wish to put the macro in your workbook as opposed to in a module. Again replace “Msgbox “Hello”" with your macro command:
Private Sub Workbook_Open()
'
'MACROS BY EXCELZOOM.COM
'
Msgbox "Hello"
End Sub
Whenever copying a list into Excel from the internet, you run the risk of pasting in unwanted hyperlinks. Sure, you could paste special as text, but sometimes it’s format was just fine the way it was. With this macro, just select all the cells with hyperlinks (even those without hyperlinks are ok to be selected) and run the macro. In a couple seconds all links are gone and your formats remain in tact.
Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Sub Remove_Link()
'
'MACROS BY EXCELZOOM.COM
'Macro to remove hyperlinks from a range of cells
'
Dim cell As Range
For Each cell In Selection
Selection.Hyperlinks.Delete
Next
End Sub
Custom views are a useful way to set, all sorts of ways to view your data, including print areas. You can set up a sheet to have several custom views set to print different areas of your worksheet. It would be nice though to be able to set a macro to automatically print the pre-defined print area for you.
Copy the code below into your Visual Basic Editor once for each custom view that you would like to print. Change the words your_custom_view_name to whatever you named your custom view. Note: “macro_name” after the word “Sub” below is the name of the macro, and has to be unique if you have several custom views, but can be whatever you’d like. I would suggest calling each macro something like “print_your_custom_view_name”, so it’s easy to remember which macro to run.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Sub macro_name()
ActiveWorkbook.CustomViews("your_custom_view_name").Show
ActiveWorkbook.PrintOut
End Sub
This macro takes whatever values are in a selected range of cells and multiplies them by -1. This is especially useful for accountants who might get a list of client data as absolute values. In accounting world, however, the debits and credits might need to have a sign reversed. With this macro, just select the cells you want reversed, and run the macro.
Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Sub negative_cell_value()
'MACROS BY EXCELZOOM.COM
'Makes data negative in selected cells
Dim cell As Range
For Each cell In Selection
If cell <> "" Then
cell.Value = cell.Value * -1
Else
'skip blank cells, or they'll get set to zero
End If
Next
End Sub
These macros change the default direction after pressing enter, of down, to whichever direction you specify. Particularly useful if entering data across several columns rather than down a single row. It keeps you from having to click through all the Excel menus to find where this option is located in Excel. Direction variables are as follows: Up: xlUp; Down: xlDown; Left: xlToLeft; Right: xlToRight.
Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Move Up:
Sub move_selection_up()
'MACROS BY EXCELZOOM.COM
'Moves selection up on enter
'
Application.MoveAfterReturnDirection = xlUp
End Sub
Move Down:
Sub move_selection_down()
'MACROS BY EXCELZOOM.COM
'Moves selection down on enter
'
Application.MoveAfterReturnDirection = xlDown
End Sub
Move Left:
Sub move_selection_left()
'MACROS BY EXCELZOOM.COM
'Moves selection left on enter
'
Application.MoveAfterReturnDirection = xlToLeft
End Sub
Move Right:
Sub move_selection_right()
'MACROS BY EXCELZOOM.COM
'Moves selection right on enter
'
Application.MoveAfterReturnDirection = xlToRight
End Sub
This macro will take the values in a given range and multiply them by whatever value you specify in the code. The code currently multiplies by 1,000,000, however replacing the 1000000 in “cell.Value = cell.Value * 1000000″ with whatever value you need. Use caution with this macro, as it will convert any formulas to a number multiplied by whatever value you specify (i.e. if cell A1 equals 1; running this macro on a cell referencing A1 will produce 1,000,000, not =A1*1000000).
Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Replace the xxxxxx in the code with whatever value you want to multiply your data with (i.e. 1000, 1000000, etc.).
Sub muliply_by_x()
'MACROS BY EXCELZOOM.COM
'Multiplies data by x (hardcodes, will lose formula)
Dim cell As Range
For Each cell In Selection
If cell <> 0 Then
cell.Value = cell.Value * xxxxxx
Else
'skip blank cells, or they'll get set to zero
End If
Next
End Sub
This macro has the same functionality of the “Multiply by X” macro, however it allows you to keep your formulas in tact, by simply adding *1000000 (or whatever value you specify) to the end of the formula (i.e. =A1 will become =A1*1000000, not 1,000,000 as per the example here).
Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Replace the xxxxxx in the code with whatever value you want to multiply your data with (i.e. 1000, 1000000, etc.).
Sub mult_by_mil_bycell_keepformula()
'MACROS BY EXCELZOOM.COM
'multiplies FORMULAS by x
Dim Orig_formula As String
Dim formulaRg As Range
Dim formcell As Range
For Each formcell In Selection
Orig_formula = formcell.Formula
Orig_formula = Mid("", 1, 1) & Mid(Orig_formula, 1) & "*xxxxxx"
formcell.Formula = Orig_formula
Next
End Sub
Sometimes it is useful to know how many times a macro has been run. For example, if you run a macro once a week to perform a particular task, but don’t want it run more than once a week, having a counter might be helpful.
Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module. You can also add other macro commands after the “Range(“A1″) = macrocount” line. This will allow you to count how many times those macro commands have been run.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Sub Counter()
'
'MACROS BY EXCELZOOM.COM
'
macrocount = Range("A1") + 1
Range("A1") = macrocount
End Sub
Have you ever set the fill color of an entire sheet (or workbook) to white just because you didn’t want to see the gridlines? Well if you have a large file, you could literally reduce the file size in half by not coloring the cells. Simply remove the gridlines (after all that’s what you wanted to do in the first place isn’t it?).
Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Gridlines Off:
Sub gridlines_off()
'
'MACROS BY EXCELZOOM.COM
' Turn Excel Gridlines Off
'
ActiveWindow.DisplayGridlines = False
End Sub
Gridlines On:
Sub gridlines_on()
'
'MACROS BY EXCELZOOM.COM
' Turn Excel Gridlines On
'
ActiveWindow.DisplayGridlines = True
End Sub
Do you have a large macro that causes the screen to flash a lot? This can be avoided with two simple lines of code to insert before and after your macro. “Application.ScreenUpdating = False” goes directly before your macro commands and “Application.ScreenUpdating = True” goes directly after your commands, and before the “End Sub”.
Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module. Replace **Your Macro Code Here** with your macro’s commands.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Sub BigMacro()
'
'MACROS BY EXCELZOOM.COM
'
Application.ScreenUpdating = False
**Your Macro Code Here**
Application.ScreenUpdating = True
End Sub