Split Function

The Split function is a VBA function that is used for breaking strings into an array. The separation of string is identified by indicating a character or set of characters which are called delimiters. This function comes in handy, for example, when you have a list of whole names of people in a column of your worksheet and you want to split them in order to get the first and last names of each person.

Read More »

How to Filter Data with Inserted Files

When you insert a file into your worksheet, it does not move with the cell by default and does not behave as if it is inside that cell. This can be a problem especially if you want to filter or sort the columns of your data just like in your case. The solution is to simply modify the Object Positioning property of the inserted file.

Read More »

Dependent Combo Boxes in a Userform

In this post a UserForm with dependent ComboBoxes is developed. The steps required to create a basic Userform with controls are explained in detail including the VBA code to drive the ComboBox functions.

Read More »

Generate a List of Random Numbers

Sometimes a list of random numbers is required for temporary passwords, pin codes, or just dummy data for your database application. This can easily be produced without having to key in multiple entries. In the sample we use the RANDBETWEEEN function which returns a random number between the ranges you provide in the function's arguments. You can follow the simple example below that will give a 5-digit random number.

Read More »

Highlight a Row based on Values in the Row

Request was received to highlight rows dependent on values in specific columns of data. A macro was developed to run through the worksheet and set a value and format for the rows based on the compared columns of information.

Read More »

CTRL+F1 to Hide the Excel Toolbar using VBA

There are a number of options to solve this problem, the simplest approach we found for this request was the combination of the "SendKeys" function and the Workbook_Activate procedure. Basically we created a routine that would Hide the Toolbar each time the workbook was activated. Then when the workbook was closed or de-activated it would reset the Toolbar to the original state.

Read More »

Transfer most Recent Records

A macro developed to compare rows of data and then transfer unique rows from the source worksheet into a result worksheet. This can be applied across many different uses in other spreadsheets.

Read More »

INDIRECT to extract a value from another worksheet

You can see that we have the Worksheet name in Column A and the value that is needed to be extracted in Column D. The supplied worksheet included a manually entered formula to pull through the value from the other worksheet. We will now demonstrate how to create a formula using the INDIRECT function that will automatically pull through values from other worksheets without manually entering each worksheet name and cell reference.

Read More »

Retain Trailing Zeros in a Text String

For this request we needed to ensure that a trailing zero was always displayed for a calculated text string. Through the use of an additional function the string included trailing zero's

Read More »