Posts by Year

2021

Check If A Cell Contains Specific Text

less than 1 minute read

How it Works Using SEARCH and ISNUMBER When you need to check if a cell contains specific text (or string) we need to combing the SEARCH and ISNUMBER functio...

Back to top ↑

2020

Repeat Values N Times

less than 1 minute read

Here is a method to repeat the values in Column ‘A’, (n) number of times required in Column ‘B’ outputting the results in Column ‘C’. The Formula is an Array...

Back to top ↑

2019

Put A Row Number On A Table

less than 1 minute read

Here’s a simple way to get the row number of the table items. It’s a simple formula referencing the current cell’s row minus the row number of the table head...

List All Vba Environment Variables

less than 1 minute read

This VBA Environment function grabs information about your operating system and returns the information as a string. The Environ function is useful for custo...

Colour Banding With Conditional Formatting

1 minute read

Use Conditional Formatting from the Format menu to apply a format to the cells. The formula used in the Conditional Formatting rule is based solely on the ro...

Import Csv To Excel With Paramaters

1 minute read

This uses the StartImportCSV module to call the ImportCSVFile function, with set parameters to import a csv file. You can muck around with this and use other...

Prevent Sheet Being Deleted

less than 1 minute read

Title: “Worksheet event to prevent the sheet being deleted” layout: single classes: wide categories: vba tags: validation developer function date: 2...

Back to top ↑

2018

Example Of Print Setup For The Active Sheet

less than 1 minute read

Example of Page Setup for the active sheet for printing example has a header, footer, narrow margins and the Column width to the page but not the rows.

Delete The Selected Row In The Active Table

less than 1 minute read

Delete the selected row in the active table will prompt the user that the selected cell in a table is about to be deleted and to Yes to confirm as deleting w...

Create Sheets From Cell Values

2 minute read

This is one of my favourites and I use it all the time. Type a bunch of sheet names in a list of cells, select those cells then run this macro.

Word Count From Cells

less than 1 minute read

There’s no built in way to count the number of words in Excel, but using the following formulas will get it done.

Extract Nth Word From A String

less than 1 minute read

If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 ...

Calculate Total Sales With An Array Formula

1 minute read

If we had to calculate Total Sales the normal way, we would have to create a ‘helper column’ for the Totals column and then enter a formula to Sum all the To...

Top Values In A List

1 minute read

From a list of 30 people with an assigned value, generate a smaller list that are the top n values in that list.

Special Characters And Symbols

1 minute read

You can always use the Insert –> Symbols method to enter characters onto your spreadsheet but you can also add them via a formula.

Generate A List Based On Criteria

2 minute read

Generate a list of items based on a criteria that doesn’t show any blanks or spaces. 20 Fruits and Vegetables listed below, choose either a fruit or vegetabl...

Index And Match

5 minute read

This tutorial demonstrates the key strengths of Excel’s INDEX / MATCH function that make it superior to VLOOKUP. You will find a number of formula examples t...

Formula To Create A Guid

less than 1 minute read

A GUID (or UUID) is an acronym for ‘Globally Unique Identifier’ (or ‘Universally Unique Identifier’). It is a 128-bit integer number used to identify resourc...

Check Value Exists With Countif

1 minute read

When checking if a value exists in a range, using COUNTIF is quicker, easier and less prone to errors. This table shows the returning values for each formula...

Create A Named Range And Choose The Scope

6 minute read

The below 2 examples create a named range for either a workbook scope or worksheet scope. Below the subroutines is more information on the differences and ad...

Copying Data Worksheet To Worksheet

less than 1 minute read

This will copy formulas from one place to another and then Copy & Paste the calculated results as Special Values There are 2 components, one is the long ...

Copy A Formula Down A Dynamic Column Range

1 minute read

Copy Formula down a dynamic Column Range with or without AutoFill or using FillDown You can use the following methods to Copy Formula down a dynamic Column R...

Converting Column Numbers To Letters

1 minute read

Obtaining Row information is easy since Rows are always Numbers. Column Letters that can be used in a Range are a little more tricky. I have many methods to ...

Clean Data With Vba

1 minute read

What this code allows you to do is circumvent testing (ie looping) each individual cell and handling trimming (removing leading and ending spaces) and cleani...

Centre Userform For Dual Or Multiple Displays

less than 1 minute read

Force the userform to load to the centre of the active excel window, add this code to the userform Initialize event. Perfect for when you have more than 1 ac...

Create A Table Of Contents

6 minute read

This one is pretty sweet. It creates a nicely formatted TOC sheet and a back button on each sheet that links to the TOC. If you happen to add a sheet, simply...

Back to top ↑

2017

Custom Right Click Menu (context Menu)

3 minute read

Easily add a custom right click menu for both normal ranges and in tables! First we need to put two codes into the ThisWorkbook Module, these 2 codes call ma...

Back to top ↑