Male Female Customer Infographic

So I am on the infographic trail again.  I have been doing a 3 part series for CPA Australia and have decided to make a few additional Excel infographics just for a bit of fun on the side.  With all of the inforgarphics on the site I have not built complex calculation engines.  I simply put a visual display together which is driven by the contents of Excel cells.  The result is an easy to maintain and update visual report.  With any luck some people might find the results useful in the work they do.

Read More

Excel VBA Filter by Icon Sets

Excel has been improving the autofiltering capabilities and this single topic forms the topic which I have happened upon the most.   I am probably like a lot of developers who had their eyes opened by the Excel loop through a range when you set criteria and Excel does the isolation for you.  The problem with this method occurs when you need to loop through thousands of rows.  This can slow your procedure considerably.  Using the autofilter with VBA by contrast is very quick and the time difference between a small list and a large list is negligible.  More recently Excel has introduced the ability to filter by icon sets.  The conditional formatting coloured arrows or chart indicators which appear in cell.

Read More

Excel VBA Autofilter on Multiple Criteria

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.

Read More

Excel VBA to Remove Data Which Does Not Match

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. 

Read More

Excel Copying Variable Columns with Array

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!

Read More

Excel VBA Send Files to Zip Drive

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.

Read More

Excel Create Child Sheets From Master

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.

Read More

Scandinavian Infograpic in Excel

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.

Read More

Open Excel Update File

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

Read More

Excel Change Print Area with VBA

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 More
Tags , , , ,

Read Individual Columns to an Array

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.

Read More

Excel Import Data from Multiple Cells

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.

Read More

Excel What Column is Filtered

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.

Read More

A Dynamic Pareto Chart in Excel

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

Read More

Excel VBA Number of Printable Pages

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.

Read More

Union An Excel Range and Transpose

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.

Read More

Excel Maximum Cell Length in a Range

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)

Read More

Excel VBA Filtering on Time

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.

Read More

VBA Check if Sheet Exists

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.

Read More
Tags , , , ,

Red, Green, Blue Interior Cell Colour

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.

Read More