Clear Data from an Excel Table
Using formatted tables in Excel is best practice. If you regularly overwrite data in a formatted table here is a quick way to clear the data before pasting in updated data.
(more…)Capturing Formulas in Excel
Unfortunately, it is easy to delete a formula. Of course, there is always Undo but if the file has been closed getting the formula back is difficult unless…..
(more…)Listing Range Names in Excel
Excel has Paste List option to list the range names in the file. Here’s a macro that does it in one click.
(more…)Macro to Set Sparkline Axis to Zero
When creating sparkline charts in Excel one of the problems is that the vertical axis doesn’t always start at zero. It is a manual process to reset the vertical access to zero and a macro can make it much quicker and easier.
(more…)Hiding Zeroes in Excel
Excel has a sheet setting that allows to stop zeroes from displaying in the whole sheet. There is a simple macro line of code that makes it easy to change.
(more…)Excel Copy Across Macro
When you work with dynamic arrays and spill ranges you get used to the formula automatically spilling across to populate a range. When you aren’t using dynamic arrays, it can be frustrating to have to copy formulas across. Here’s a simple macro solution. In the image below I need to copy cell C3 across to the range D3:N3. This is a standard budget layout in Australia where the financial year starts in July. A one-line macro command can copy this…
Excel VBA – Please Don’t Do This
I recently read an Excel VBA book that recommended using numbers instead of VBA constants for message boxes. Please don’t do that. Here’s why.
(more…)Excel VBA and the TRUNC Function
Excel has a TRUNC function that truncates numbers. For example =TRUNC(15.75) returns 15. It doesn’t round it just removes the fraction leaving the whole number. Excel VBA does not have the TRUNC function. It does have the Fix function that works the same.
(more…)Excel VBA to Get to the Top Left of the Screen
Sometimes when you are using Excel VBA you need the screen to always display at the top, left of the sheet. Here is how you do it.
(more…)Quotation Marks in Excel VBA
Sometimes when you are building a text string in VBA you may need to insert quotations marks. That poses a problem because quotation marks are used to surround the text you want to join and using them within the text is problematic. There is a solution.
(more…)Excel VBA Sort Sheet Tabs by Colour
Using colours on your sheets can help you navigate and organise your file. If you want to sort your sheets by colour you can use a macro to speed up the process.
(more…)Excel File is Open VBA Function
When working with files in VBA code, you may need to know if a file is already open. A function can return TRUE if a file is currently open and FALSE if it isn’t.
(more…)Sheet Exists Function for Excel VBA
Often when you are working with Excel VBA you need to confirm if a sheet exists based on the tab name. This Function can be used to do that. It works with the active file (workbook).
(more…)Clearing Unlocked Cells in Excel
People always ask, how to do you lock Excel? In fact you have to unlock Excel and then protect the sheet. The default setting for all cells is locked, so you need to unlock input cells.
(more…)Save and Close extra
In my previous post I created a macro from scratch that saved and closed the current file. The macro required that the file had been saved before and wasn’t read only. This post handles those two situations so you can run the macro on any file and it will only work when required.
(more…)Save and Close in Excel
I started using Excel in the late 80’s on a Mac. It had a Save and Close button. When I discovered VBA in Excel on the PC, the very first macro I ever made was save and close.
I thought this would be a good example to take you through creating a macro from scratch and sharing a technique to make it easy to use.
(more…)Use an Outlook template for an email
Macros are designed to reduce keystrokes and mouse clicks. Here is a perfect example. It takes seven separate mouse clicks to use an Outlook email template as a new email. Here is a macro that does it in one.
(more…)Excel VBA to confirm a range is selected
One of the most powerful statements in VBA is Selection. This holds whatever the user has selected just before they ran the macro. Normally it is a range, but what if it isn’t?
(more…)Excel VBA to select the first cell in a range
If you need to refer to the first cell (top, left cell) in a range there is an easy way to do it.
(more…)Activating a Sheet When an Excel File is Opened
There are times when you would like to have the same sheet visible each time a file is opened. You can achieve that with a Workbook Event macro.
(more…)Excel Macro to Insert a Formula
When I am creating a file for my training or for my blog or other articles that I write I regularly use the FORMULATEXT function to display the formula in a cell on the right of the actual formula. To save time I created a macro to do the work for me.
(more…)Extract Email from Copied Outlook Address
I frequently copy an email address from Outlook to Excel and most times it looks like John Smith<jsmith@email.com>. To be used as an email I need to extract from between < and >. To do that in a single cell is tedious, so I wrote a macro to do it for me.
(more…)Increase Font Size in VBA Window
Yes, you can make the text in the VBA window easier to read. It is also great for training. (This tip may not work on 4K monitors.) To open the VBA Window press Alt + F11. Click the Tools menu and then Options - see image below. Click the Editor Format tab and change the Size drop down to 14, or whatever you want – see image below. Click OK and the font size will now be increased in the…
Free Webinar Recording – Recorded Macros Tips Tricks and Traps
It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file. Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names. Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel. This is the first…
Excel ISDATE Function
Unfortunately Excel doesn’t have an ISDATE function. Excel’s macro language, VBA does, but there is no spreadsheet function that let’s you know if a cell contains a date. Well there is a partial workaround and you can also use VBA.
(more…)Autofit Rows with a Maximum Row Height
In March I wrote a post on using a macro to apply column Autofit on a sheet but with a maximum column width. This is a follow up post as someone requested the same functionality for row height.
(more…)