Let’s say you are creating a new table in a new sheet using a macro and you need to create the headings in row 1. There is a reasonably easy way to do it.
Category Archives: Macros
Free Excel Webinar – recording Macros Tips Tricks and Traps
Getting started with macros
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 in a series of webinars dedicated to macros. Future paid sessions this month will expand on the techniques taught in this session.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
VBA If statement tip
When creating long VBA code it is common that the start of an If statement and the matching End If statement may not be visible on the same screen.
When scrolling around trying to understand your code it can be useful to include the If statement itself as a comment following on the same line as the End If command – see examples below.
If x=0 Then 'lots of code If y =1 Then 'lots of code End If 'If y =1 then End If 'If x=0 Then |
The apostrophe is used to specify the start of a comment – you can have a comment following a line of code.
This structure can assist when trying to identify which End If statement relates to which If statement.
Expand to Fit to One Page in Excel
Macro solution
Excel will automatically decrease the print zoom % to fit to one page, but it won’t increase the zoom % to fit to one page. E.g. if you want to print on A3 instead of A4. I had a request to do this, so I wrote a macro to do it.
Excel VBA to Clear Manual Page Breaks
Be careful how you do it
When clearing page breaks in Excel VBA you need to be careful. There is one command that will clear page breaks but it will also affect other print settings.
Declaring Variables in Excel VBA
Using the Dim statement
In a Linkedin Excel Group recently there was a discussion about whether or not you should use the Dim statement to declare your variables. The argument was that you don’t have to and someone had managed to successfully create some code without declaring variables.
Replace Merged Cells with Center Across Selection
Let the macro do the work
Unfortunately lots of people use the Merge & Center format in their spreadsheets. When working with other people’s files that contain Merged cells I will often remove the Merged cells format and apply Center Across Selection which is the preferred format to use. The macro below will convert Merged cells to Center Across Selection.
Make Excel VBA Pause
Sometimes when running a macro you need to make sure Excel has had time to do something before progressing.
This is typically in large models were it can take time (a few seconds) to do a specific task eg removing a filter or updating an external data source.
You can pause a macro to allow Excel to do something by using the Wait command.
Application.Wait (Now + TimeValue("0:00:02"))
The above code will pause the macro for 2 seconds.
Are There Hidden Rows or Columns?
A macro answers the question
I was working on a project for a client and receiving multiple files. Some of the sheets had hidden rows or columns. I realised there is no easy way to find out if a sheet has hidden rows or columns, so I wrote a macro.
VBA to Clear a Filter
Using Excel’s built-in filtering can speed up your VBA code.
It is important if you are applying filters that you clear any existing filters before you apply a new filter. Otherwise the existing filters will usually affect a new filter you apply.
The line of code below will remove filters on Sheet1 (Sheet1 is the sheet code name that you see on the left side of the VBA screen – it may not be the sheet tab name).
If Sheet1.FilterMode Then Sheet1.ShowAllData
The .FilterMode property is True if a filter is in place on the sheet and False if not.
The .ShowAllData method will return an error if no filter is in place – hence the use of the If statement.
Deleting Duplicated Names in Excel
Names scoped at Worksheet level are often duplicates
When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.
Jon Acampora
2017-04-27
2017-04-27
Export a sheet as a PDF
It takes a few clicks but it is possible
I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)
Excel Cell Comments and Documentation
Macro to Create a Comment Report for a File
I was watching a video a while back and some Excel experts were lamenting the lack of a documentation standard in Excel. They mentioned that the cell comments system could be used for documentation, but there was no way to centralise all the comments. Well, I have written a macro to do just that.
Excel and pdf
Creating a pdf from a single sheet
In Excel you can us Save As to save a file as a pdf, but it isn’t quite as effective in Excel as it is for MS Word. Often you only want to save a single sheet or a few sheets to pdf. Try this.
Ron DeBruin is an Excel MVP and has been for a long time.
His site has lots of excellent macro code and free Add-ins.
He has tips and macro examples for the Mac as well as Windows.
I have used his pdf creator code to create pdfs of certain sheets in a file.
If you want to learn about modifying the ribbon he has some great tips and examples.
He also has code examples for amending Excel’s pop-up menus.
Disabling Sheet Unhide in Excel
How to hide a sheet and make it hard to unhide
You can right click a sheet tab and select Hide, but it is just as easy to Unhide the sheet. What if you want to make it harder to unhide the sheet?
PivotTable Listing Macro
Create a list of all PivotTables in a file
If you inherit a file or you haven’t used a file for a while, it can be useful to do an inventory of all the PivotTables. A macro can do all the work for you.
VBA Window Tip
To open the VBA window press
Alt + F11
This shortcut actually toggles between the Excel window and the VBA window – but if the VBA window isn’t open, it will open it.
Populating Blank Cells in Excel
A macro to make it easier
I have mentioned before that blank cells in your data can affect Pivot Table defaults in Excel. They can also reduce the effectiveness of some keyboard and mouse shortcuts. The macro below populates blank cells in the selected range with zeros.
Nice to get emails like this
I wrote a macro to help someone fix their badly laid out data listing.
Macros rule!
Excel VBA Object Variables
Tips on using them
Object variables are the variable types that have their names written in black (right hand side) when you define them – see example image below for Range, Worksheet and Workbook. These are the most common objects used. There are two important things to know about using object variables.
Useful Excel VBA Range Technique
Titles made simple in VBA
It is common to create headings for data sheets in Excel VBA. There is an array technique that can make this a simple process.
Excel VBA BeforePrint Issue
Not all Print Previews are Equal
Excel has a BeforePrint event which enables you run VBA code before a document is printed. This event can also be triggered by Print Preview. But not all Print Previews are the same.
Excel VBA and Variables
Using Option Explicit
Variables can speed up your code and make maintenance a lot easier. You should always declare or Dim (technical term) your variables, here’s why.
Today’s Date
The formula that will always display today’s date is
=TODAY()
The keyboard shortcut to enter today’s date in the active cell as an input is
Ctrl + ;
The VBA line of code to enter today’s date in cell A1 is
[A1] = Date
VBA – Easy Way to Handle TRUE/FALSE Tests
Using a boolean variable
Let’s say you have VBA code that handles a budget and a forecast. There is a cell B2 on the Input sheet that contains the word Budget or Forecast. Based on that cell the macro with do different things. You may need to test for Budget/Forecast a few times within the code. There is an easy and flexible way to handle this.
VBA to Open CSV and Avoid Date Error
You can create a macro to open a CSV file. One problem you may face is that dates are treated as US dates. A simple change can fix this.
Japanese Yen Format in Excel
Applying it quickly
To apply the Japanese Yen format can take quite a few mouse clicks.
The macro that does it, on the other hand, is quite simple. Select the range, then run the macro.
Sub JapaneseYen() Selection.NumberFormat = "[$¥-411]#,##0.00" End Sub
If you are unsure how to use macros, see the link below.
Getting Excel to Recognise an Email Address
Converting it into a hyperlink
Sometimes when Excel imports email addresses they are not recognised as emails and are not hyperlinks. They are two ways to fix this.
A common Excel request is to be able to type characters and see the in-cell data validation drop down list reduce, based on what you have typed.
This free add-in from Jon Acampora (Excel MVP) does just that. He has recently added a few new features.
If you have long drop down lists this add-in is a great addition to Excel.
This link has a video of how it works and the new features like Auto Open when a data validation list cell is selected.