Split File into Parts and Save to Directory

• April 20, 2024
• Marcus Small

In this article we will split a file into many different subsets and save each subset to the directory of your choice.

Horizontal Range to Vertical Range in Excel

• June 15, 2022
• Marcus Small

With the advent of Power Pivot the task of flipping data is made very simple. The Unpivot Other Columns command is a bit of a game changer. It easily moves data from horizontal to the more database friendly vertical range very nicely.

Hiding Rows Quickly with VBA

• May 20, 2022
• Marcus Small

Hiding rows with Excel VBA the simple way using a union range. Typically if you want to hide rows very fast with VBA the autofilter is the tool of choice, in a single line with one swift move thousands of rows can be hidden. Amazing.

Cascading Combo Boxes 3 and 4 Layer

• April 24, 2022
• Marcus Small

Create 3 and 4 level dynamic lists into an Excel data validation on the fly by managing a vertical list with no named ranges. This is the data validation list management tool you have been looking for.

List All Sheets in a Excel Workbook

• April 17, 2020
• Marcus Small

Creating worksheet tab names in a distinct and uneatable list is a tedious task. Using Excel VBA is the answer to this problem.

Convert Formula to Relative or Absolute

• April 11, 2019
• Marcus Small

Convert an Excel range from one reference to relative or absolute referencing.

Create Number Sequence with VBA Custom Function

• January 15, 2019
• Marcus Small

An Excel custom function that allows sequental number sequences to be created.

Excel Autofill - Fill Down to Bottom of Range

• April 9, 2018
• Marcus Small

In Excel you may wish to drill down on a range which has a header value not already contained in the rows.  The following article shows a practical example of how to generate a unique row name based on the heading in a specific place.  The procedure is in VBA.

Advanced Filter a List Automatically

• July 17, 2016
• Marcus Small

I was recently working on an automated procedure to generate data in a list based on drop down (data validation) selection.  The idea is to change items in a drop down (data validation) and have the corresponding data table filter to the specific items chosen in your drop downs.  I have shown a formula based solution for this method (a long time ago) so if a more simplistic solution hits the mark - the link can be found here.

Excel Create or Remove a Table with VBA

• June 16, 2016
• Marcus Small

There are times in Excel when you may wish to create a table on the fly with the assistance of VBA code.  I was in this position recently and needed to this with VBA.  A file was uploaded into a sheet and the task was to create a table and then use that table for more data manipulation.  The following Excel VBA procedure was what I ended up with.

Excel VBA Filter by Icon Sets

• June 9, 2016
• Marcus Small

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.

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.

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

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.

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.