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.Read More
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.Read More
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.Read More
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
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
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.
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
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
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.xlsxRead More
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
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
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
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
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
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
I was recently answering a post on Ozgrid about filtering a list using a slicer. The post was very similar to a blog post of mine with a slight twist. The poster wanted the original list filtered based on the selection of the slicer and if no slicer item was selected then the filter was to be taken off the dataset. I found the problem interesting because in order to solve the problem you had to know how many slicer items were in the list in the first place.Read More
This is an alternative to conditional formatting. The following will detect the colours Red, Yellow and Green in a particular cell. It will add the name of the colour – good for those colour blind individuals. It is a custom function so needs to used in conjunction with a formula or in a VBA procedure.Read More
During a procedure you may wish your user to choose a file to open. This can slow the process of running code down but if you have a moving target this will be essential in getting the right data imported or manipulated via VBA. I have not had to do this very often but a client asked if they could choose a file mid procedure and the following is what I came up with.Read More
I was asked during a webinar recently how you send multiple worksheets to a new workbook in a batch. I was pretty sure this information would be on my site but a quick search of my site did not reveal any joy.
I put together a simple file which sends an output sheet and two source data sheets to a directory saves it then starts the process again after changing a unique identifier. The process is a little more in-depth than sending just one sheet but there is not a great deal more code involved.Read More
Convert Formula to Relative or Absolute April 10, 2019
Convert Entire Excel Workbook to Values March 11, 2019
Evaluate with Excel to Extract Sequence March 6, 2019
Extract the First Item from a Horizontal List February 27, 2019
Create Number Sequence with VBA Custom Function January 15, 2019
Protect a Worksheet, Use the Autofilter October 18, 2018
Countifs Across Sheets October 10, 2018
Excel VBA to Select Sheets September 22, 2018
Power BI Desktop Dashboard August 20, 2018
New Excel Dashboard Tools August 1, 2018