The default setting for charts in Excel is to hide the data on the chart if it is hidden on the sheet. I forgot that recently when I created a few charts using a workings area to hold the chart data. I later hid the workings with column grouping. Oops – when you hide the data in the charts go blank.
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 run the macro on any file and it will only work when required.
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.
I frequently copy an email address from Outlook to Excel and most times it looks like John Smith<firstname.lastname@example.org>. 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.
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.
The column Autofit on the whole sheet is a great Excel feature. But if you have a few columns that have lots of text it can make using it problematic as you need to manually adjust those wide columns. Here’s a macro to make it easier.
When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.
My book was published just over four years ago and part of the writing process was creating an Index. To make the task easier I wrote a macro to assist me. Adding an Index to a large document can improve its usefulness. I am sharing the file I used in this blog post.
In a recent webinar I was asked about the “Too many different cell formats” error. This tends to be an error in Excel 2010 and earlier versions. In many cases this error is caused by having too many custom Styles.
It is common knowledge (or it should be) that running a macro clears the undo list. In general you can’t undo a macro. However some macros also clear the clipboard which can stop you copying and pasting. I have found a workaround for the clipboard problem.
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.