Posts by Tag

developer

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...

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 ↑

text-strings

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.

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...

Back to top ↑

practical

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...

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.

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...

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...

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 ↑

lookup-reference

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.

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...

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...

Back to top ↑

tables

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...

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...

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...

Back to top ↑

copy-data

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...

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...

Back to top ↑

function

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 ...

Back to top ↑

interface-formatting

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...

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.

Back to top ↑

inputbox

Back to top ↑

text

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...

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 ...

Back to top ↑

validation

Back to top ↑

userform

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...

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 ↑

unique

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...

Back to top ↑

summing-counting

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...

Back to top ↑

array-formulas

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...

Back to top ↑

accounting

Back to top ↑

date-time

Back to top ↑

deleting-data

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...

Back to top ↑

dynamic-ranges

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...

Back to top ↑

files

Back to top ↑

export

Back to top ↑

deleting

Back to top ↑

practical-applications

Back to top ↑

Dynamic Ranges

Back to top ↑

Printing

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.

Back to top ↑

sharepoint

Back to top ↑