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.
Category Archives: Macros
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 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
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.