Excel VBA Autofilter on Multiple Criteria

• June 7, 2016
• Marcus Small

Anyone who has followed my posts on Ozgrid or Chandoo forum will have noticed how frequently I use the autofilter in favour of any kind of loop.  I have been lead to believe that in some instances that arrays will perform faster than the humble autofilter however for brevity of code and swiftness I really like the simple elegance of the autofilter.  In the article Autofilter on Multiple Conditions I delved into the world of the filer using more than one criteria.

Excel VBA to Remove Data Which Does Not Match

• June 5, 2016
• Marcus Small

In the article highlight column differences I touched on the VBA procedure:

Remove Column Differences

The article was introductory where I did a bit of colour coding of cells which did not match and copied the non matching cells to a fresh sheet.  Well this technique can be used in a multitude of ways.  We can remove all of the rows in a sheet which do not meet criteria as specified in a particular cell. 

For example if we had a listing of sales people we might want to isolate only a single sales person order quantity. 

Excel Copying Variable Columns with Array

• June 4, 2016
• Marcus Small

My tassie friend Valario asked another interesting and engaging question.  The question came from the blog post ‘Read Individual Columns to An Array’.  The design of the code was a little bit static so Valerio’s question was as follows.

Another question just to ruin your night!

Excel VBA Send Files to Zip Drive

• June 1, 2016
• Marcus Small

Zipping up Excel files on the fly can be a most useful activity especially if working with outlook.  You may wish to generate a set of files with Excel VBA then zip those files and send them on to a list of people for review or as part of a monthly reporting procedure.  I have seen plenty of these type of procedures.  The most famous of which is on Ron De Bruin's site.

Ron De Bruin Zipping

The idea behind the concept is to have a file path with files inside it.  The zip procedure runs and sends all of the files to a compressed zip file and saves the file in a designated folder.

Excel Create Child Sheets From Master

• May 31, 2016
• Marcus Small

Recently my friend in Tasmania – Valerio – was kindly helping someone online (the kindness of strangers).  The problem was as follows.  The person had a master list and wanted to create child sheets from this master list.  Sort of a parent to child type exercise where a single list will produce multiple sheets (one to many - see image above).  The problem arises when you have some sheets which have already been created and some new sheets which need to be created.  As such we need to test for the existence of the sheet.  In the following article I explore the VBA code required to test if a sheet exists without the use of a traditional custom function.

Scandinavian Infograpic in Excel

• May 30, 2016
• Marcus Small

The above infographic is one of my favourites of the Nordic region. It is very simple and yet tells a story about 4 countries in Scandinavia. The Excel infographic has the top 5 companies for each country (Iceland is excluded due to its small size - sorry). It has data on career and staffing data. There is some population metrics, GDP and GDP Growth which is low all around the world as a result of the global downturn after 2008. Let's hope that ends soon.

Open Excel Update File

• May 23, 2016
• Marcus Small

At times you may wish to open a workbook, add a couple of items from the workbook you are working in to a list and close the workbook.  This type of update might be done on a specific set of cells and the results are added to the bottom of a list in a destination workbook.

Let's say we have data in Cells B10:B11 and we want to update our master workbook.

In cell B9 I have a path:

B9 = D:\Example1.xlsx

Excel Change Print Area with VBA

• May 12, 2016
• Marcus Small

Setting the print area on a moving range can be a difficult and frustrating thing to trap.  You may wish to trap a specific range with your print area or have the print range only consider certain columns.  Let's say you have a couple of helper columns in an Excel workbook and don't want these columns to be printed but you want the columns to be visible.  You could manually set the print area to exclude the print area but now you have more data which you have added and you would like the print area to update in kind.

Read Individual Columns to an Array

• April 12, 2016
• Marcus Small

In Excel VBA as I have shown previously you can push data between 2 or more arrays.  There have been a glut of examples on this website where I have moved data from one array to another.  There have even been examples where I have moved only certain columns into a single array.  The following method with you the INDEX formula within VBA to move data from one array to another but only move the columns which are specified.  With the following method of the 6 column array (Cols A – F) only columns A, C and E will be moved from the first variant, to the second variant.

Excel Import Data from Multiple Cells

• April 9, 2016
• Marcus Small

Recently a client asked me if I could create an Excel VBA procedure which picked up data from a file but the data came from multiple sheets and multiple cells in non sequential locations. Firstly, I thought the best method to do this would be to have a summary sheet which is hidden and simply pick this sheet up and consolidate it in the parent workbook.  However the problem was the files had already gone out and over 200 Excel files needed to be consolidated into a single workbook.

Excel What Column is Filtered

• April 8, 2016
• Marcus Small

Have you ever been using a large Excel file and wondered which cell am I actually filtering here?  Especially if you pick up someone else's file.  There might be more than one column filtered and there may be hundreds of columns.  You tend to go blind after a brief period of looking for that column with a blue arrow on the filter.  Well there is an alternative.  What if every time you filtered the worksheet a colour appeared in the cell(s) you were filtering.  You could see instantly which column was the source of the filter.

A Dynamic Pareto Chart in Excel

• April 4, 2016
• Marcus Small

A Pareto chart in Excel is a chart which is made up of a column chart and a line chart to show a cumulative position.  This is often a good chart to show an 80 20 scenario, where 80 % of the costs are achieved by 20% of the cost drivers.  The chart can be created by segmenting the the data into cost drivers or buckets.  If your business expenses were allocated into the following buckets:

• Staff

• Building

• Legal

• Accounting

• Rates

• Entertainment

• Marketing

Excel VBA Number of Printable Pages

• March 31, 2016
• Marcus Small

When I was younger the hashtag symbol was universally recognised as the symbol for a number.  Now it appears as the opening character in a tweet or other social media post.  I was recently asked to generate a procedure which would add the page numbers which were to be printed to the bottom of an Excel sheet.  The idea was when the file printed the first page had a description which said there will be X number of pages in the current report.  Excel does not currently have a generic report pages generator algorithm so here is a starting point.

Union An Excel Range and Transpose

• March 23, 2016
• Marcus Small

I was asked by a colleague to transpose a range in blocks of 5 from horizontal to vertical.  The data was arranged in 7 columns but he wanted the data split into from tabular data to headings on the vertical and the body text going across the columns.  Each block of data was colour coded and this colour scheme was meant to be kept in the output page.  It is an interesting problem and I decided to include a union range so I could include the header row (giving meaning to the data) each time a block of 5 rows was copied and transposed to the new sheet.

Excel Maximum Cell Length in a Range

• February 29, 2016
• Marcus Small

Finding the maximum length string in a range of cells can be handy especially if you are using the max length for another purpose.  The following custom function will find the maximum cell length in a range.

Function LongString(rng)

    LongString = Evaluate("Max(len(" & rng.Address & "))")

End Function

The custom function is called as follows:

= LongString(A1:A10)

Excel VBA Filtering on Time

• February 28, 2016
• Marcus Small

When filtering data the website has focused acutely on filtering information which meets a certain criteria or between two criteria.  The site has dealt with filtering on multiple criteria and filtering between dates.  Some of these concepts are quite difficult as repeating Excel's tick by filter is not always easy to replicate in VBA.  I came across an interesting problem while working for a client.  The range of cells had the date and time in the cell, the idea was to get quantity data from a dataset if it was past a certain time.

VBA Check if Sheet Exists

• February 21, 2016
• Marcus Small

I have a solution for checking if an Excel worksheet exists or not.  This can come in handy if you are creating a new sheet based on  a dataset.  You check for the new sheet and if the sheet exists you can alter the old sheet if the sheet does not exist then you can create a new sheet.

If Not Evaluate("ISREF('" & [A1] & "'!A1)") Then

Where Excel cell [A1] contains the name of the sheet you are checking for.

Now if this procedure was part of a loop you may wish to check all of the cells from say A1 to the last used row in column A.  Then the following might be more relevant.

Red, Green, Blue Interior Cell Colour

• January 24, 2016
• Marcus Small

Recently I was giving a half day course on heat maps and came up with the novel idea of creating a custom function which would identify the primary colour scheme for a cells interior colour.  It is in an effort to save a little time in the creation of a colour scheme for heat mapping.  Rather than laying down the colour and looking up the Red, Green and Blue numerical combination I simply lay the colour down and the custom function does the work for me.

Copy and Paste an Excel Chart into PowerPoint with VBA

• December 22, 2015
• Marcus Small

Creating Powerpoint presentations automatically is handy if you want the power to control another office application.  In this post I will copy a chart in Excel and paste it into a fresh instance of Power Point.  I will show two examples, once where the data will be pasted into a presentation with a title and one without.   This is a building blocks approach, creating a procedure which pastes one chart might seem like time better spent done manually but if there were 20 charts it would be faster for VBA to do the work for you. 

Add Picture to Excel Cell

• November 26, 2015
• Marcus Small

I noticed a post today on Ozgrid which asked if it were possible to create a situation where a picture could be added to a cell.  It got me thinking perhaps there is a way without the use of VBA.  So a bit of research and I happened upon this great technique, adding the picture to a comment.  This way your picture will appear as you hover over the cell.  There is no VBA required.  The following are the steps to take to achieve the task.

Insert comment in cell E3 right click the outer border and choose: