Excel Zoom

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

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


So you’ve found the perfect macro code to speed up that tedious and repetitive task – now what? In this section, we’ll walk you through the several ways to install and use your macro code.

First, MAKE A BACKUP COPY OF YOUR FILE! There are some macros that can’t easily be undone once they are run. It is always important to have a back-up copy of your work incase something goes unexpectedly wrong.

Record a Macro:

The first, and easiest way is to let Excel do the work for you. In earlier versions of Excel (pre Excel 2007), click ‘Tools’ -> ‘Macro’ -> ‘Record New Macro’. In Excel 2007, click the ‘Developer’ menu then ‘Record New Macro’. Enter in the appropriate information in the Record Macro dialog box (macro name, shortcut key (if desired), location to store the macro, and description (if desired)), then click ‘OK’. Excel will record every command and keystroke you make until you click the ‘Stop Recording’ button. There’s no need to enter any additional information, Excel has everything it needs to run your macro!

If you found the perfect macro to use on Excel Zoom’s macro list, you’ll need to “install” it before using it. There are several ways to do this and which one you choose depends on how you are planning to use the macro.

Single Workbook Use:

If you only want the macro to run in one particular workbook, and don’t want it available everytime you open Excel, this is where you want to be.

• Open up the workbook you wish to use the macro in. This can be a new or existing workbook, it doesn’t matter. For illustrative purposes these instructions will assume you’re using a new workbook, called “Book1″
• In pre-Excel 2007, click ‘Tools’ -> ‘Macro’ -> ‘Visual Basic Editor’ or simply press ‘Alt+F11′. In Excel 2007, click the ‘Developer’ menu then ‘Visual Basic’.
• A new window should have popped up that looks nothing like Excel. Don’t be alarmed this is what we want. In the left hand pane you should see a tree that says something along the lines of “VBAProject (YourExcelFileName)”, obviously replacing YourExcelFileName with, well, your Excel file’s name. Mine says “VBAProject (Book1)”. Under that there should be a folder, called “Microsoft Excel Objects” that lists all the worksheets in your file.
• Right click on ‘VBAProject (YourExcelFileName)’ -> ‘Insert’ -> ‘Module’. This should create a second folder under “VBAProject (YourExcelFileName)”, called “Modules”. You’ll also notice a blank screen over in the right hand pane. This is where we’ll be pasting in your macro code. Note: If you already have a second folder called “Modules”, you can skip this step, and just double click on “Module1″ in the folder.
• Now, go back to the wonderful time saving macro you found on ExcelZoom.com, click in the text area, which automatically selects all the code for you (cool isn’t it), and copy it (Ctrl+C, or right click and Copy).
• Go back to your Visual Basic Editor (that thing you opened that doesn’t look like Excel). Right click in the blank right hand pane and paste in your code. It’s ok if you already have macros there and this pane isn’t blank, simply scroll down to the bottom and paste the code after your last macro.
• Now, close the Visual Basic window, you’re done there. Go back to your workbook and click ‘Tools’ -> ‘Macro’ -> ‘Macros’ in pre-Excel 2007, or simply press ‘Alt+F8′. In Excel 2007, go to the ‘Developer’ menu and click ‘Macros’.
• A list of all available macros should appear, including your recently installed macro. Select the macro in the menu you wish to run and click the ‘Run’ button. Then sit back, relax, go get a cup of coffee, take a nap, whatever you think you’ve earned with all that extra time you have!
• It is also important to note that there are other ways to run your macros, which are covered below.

Make a Macro Available to All Workbooks Using PERSONAL.XLS:

Suppose you have a macro that you want to be able to use in any Excel file you open. No problem! Follow the steps below, and you’ll have access to your macros in any Excel file you open.

• First, you’ll need to make sure that you have a Personal Macro workbook. It should be stored in the folder that Excel uses to open all files at startup in. To find which folder this is, click ‘Tools’ -> ‘Options’ -> ‘General’ tab, and find the file location labeled “At startup, open all files in:”. Mine is set to “C:Program FilesMicrosoft OfficeOffice10xlstart”, yours should be something similar, unless you’ve changed this. In any case, click ‘Cancel’ and go to the folder specified in that box.
• If there is a file in that folder called “PERSONAL.XLS”, open it, otherwise, right click and select ‘New’ -> ‘Microsoft Excel Worksheet’. Call it PERSONAL.XLS and open it.
• From here, the steps are the same as if you were planning on using the macro in a single workbook (above).
• In pre-Excel 2007, click ‘Tools’ -> ‘Macro’ -> ‘Visual Basic Editor’ or simply press ‘Alt+F11′. In Excel 2007, click the ‘Developer’ menu then ‘Visual Basic’.
• A new window should have popped up that looks nothing like Excel. Don’t be alarmed this is what we want. In the left hand pane you should see a tree that says something along the lines of “VBAProject (YourExcelFileName)”, obviously replacing YourExcelFileName with, well, your Excel file’s name. Mine says “VBAProject (PERSONAL.XLS)”. Under that there should be a folder, called “Microsoft Excel Objects” that lists all the worksheets in your file.
• Right click on ‘VBAProject (YourExcelFileName)’ -> ‘Insert’ -> ‘Module’. This should create a second folder under “VBAProject (YourExcelFileName)”, called “Modules”. You’ll also notice a blank screen over in the right hand pane. This is where we’ll be pasting in your macro code. Note: If you already have a second folder called “Modules”, you can skip this step, and just double click on “Module1″ in the folder.
• Now, go back to the wonderful time saving macro you found on ExcelZoom.com, click in the text area, which automatically selects all the code for you (cool isn’t it), and copy it (Ctrl+C, or right click and Copy).
• Go back to your Visual Basic Editor (that thing you opened that doesn’t look like Excel). Right click in the blank right hand pane and paste in your code. It’s ok if you already have macros there and this pane isn’t blank, simply scroll down to the bottom and paste the code after your last macro.
• Now, close the Visual Basic window, you’re done there. Go back to the file called “PERSONAL.XLS”. This is where it gets a little different from the steps for a single file use above.
• Click ‘Window’ -> ‘Hide’. Considering that this file will open everytime Excel opens, you want it to open in the background, where you won’t see it. If you ever wish to add/edit/delete a macro in PERSONAL.XLS, just click ‘Window’ -> ‘Unhide’ and select PERSONAL.XLS from the list.
• Close Excel. If it prompts you to save PERSONAL.XLS, click yes to save it.
• Open a new or existing Excel file. You should only see that file open up, not PERSONAL.XLS as it was set to open in the background.
• Click ‘Tools’ -> ‘Macro’ -> ‘Macros’, or press ‘Alt-F8′. You should see a list of macros that were entered into PERSONAL.XLS. They will be “prefixed” by “PERSONAL.XLS!” then the macro name. They’re ready to use and be run in any workbook you open.
• See below for ways to run your macros.

 Running your Macros

There are three ways to run your macros. 1) Using the ‘Tools’ menu; 2) Creating a toolbar button; 3) Assigning a keyboard shortcut to your macro.
The following assumes that you already have macros available to use.

1) Using the ‘Tools’ Menu:

• Select the cell (or cells) you wish your macro to modify, if necessary.
• Click ‘Tools’ -> ‘Macro’ -> ‘Macros’ in pre-Excel 2007, or simply press ‘Alt+F8′. In Excel 2007, go to the ‘Developer’ menu and click ‘Macros’.
• Select the macro you wish to run.
• Click ‘Run’.

2) Creating a Toolbar Button:

• Click ‘Tools’ -> ‘Customize’ -> ‘Commands’ tab.
• In the ‘Categories’ menu, select ‘Macros’.
• Select ‘Custom Button’ and drag it to the toolbar position where you want it.
• In the ‘Commands’ tab, select ‘Modify Selection’ -> ‘Assign Macro’.
• Select the macro you wish to assign to that button and click ‘OK’.
• You can modify the button, by selecting ‘Modify Selection’ again, click ‘Change Button Image’ and select a different image.
• If you wish to add text to the button, select ‘Modify Selection’, change the description in the ‘Name’ box, and select either ‘Image and Text’ or ‘Text Only’ depending on which you prefer.
• Play around with some of the other options to get the custom button exactly the way you want it.

3) Assigning a Keyboard Shortcut to Your Macro:

• Click ‘Tools’ -> ‘Macro’ -> ‘Macros’ in pre-Excel 2007, or simply press ‘Alt+F8′. In Excel 2007, go to the ‘Developer’ menu and click ‘Macros’.
• Select the macro you wish to assign a shortcut to and then click ‘Options’.
• Type the key you wish to use with CTRL in order to run your macro.
• IMPORTANT: Do not use a key that is already associated with other Microsoft functions (i.e. CTRL+A already is a shortcut to select all).