## Indirect Sheet Referencing To Calculate Ranges

1 minute read The INDIRECT function returns a reference to a range. You can use this function to create a reference that won’t change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells. Download the example workbook here: Indirect Sheet Referencing … Read more

1 minute read A technique to add a custom right click menu (context menu) to a text box on a UserForm. There is no system context menu for text boxes in a userform, and the good news is that you can do this for as many text boxes in a userform as you like! This … Read more

## Function Check Multiple Named Ranges Exist

less than 1 minute read Function to check if multiple named ranges exist ‘============================================================================== ‘ ## Function: Check multiple named ranges exist ‘============================================================================== Function RangeExists(s As String) As Boolean On Error GoTo myExit RangeExists = Range(s).Count > 0 myExit: End Function ‘ Function example: Sub CheckRanges() Dim vNames As Variant, v As Variant vNames = … Read more

## 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. Download the example workbook here: Top n Values in a List.xlsx How it all works Our source data is in A1:D31 and Output begins in H1. All formulas have … Read more

## Get The Week Or Fortnight Ending Dates With A Formula

3 minute read These formulas are great for when you have to find the week ending date or even the fortnight ending date for a specific item. Week ending dates don’t have any dependencies, however can use a cell value as a date reference instead of hard coding the date. Calculating which fortnight a date … Read more

## Text Values Strings Words And Phrases

1 minute read Example: =LOWER(“hello”) Result: hello Convert text to upper case Example: =UPPER(D3) Result: JAMES Convert text to proper case (each word’s first letter capitalized) Example: =PROPER(“this is a long sentence”) Result: This Is A Long Sentence Combine different text values to one text Example: =CONCATENATE(A3, ” and “, A4) Result: 30 and 25 … Read more

## Export And Save Selected Sheets To A New Workbook

8 minute read User can save the new workbook as file types: xlsx, xlsm, xlsb, xls, csv and txt with the following features: external links are broken formulas that reference sheets outside the sheet in the new workbook are changed to values VBA code within modules will not copy to the new workbook however Worksheet … Read more

## 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. ‘ ================================================================== ‘ ## Example of Page Setup for the active sheet for printing ‘ This example has a header, footer, narrow … Read more

## Formula To Get File And Folder Names

1 minute read Let’s image that the following path is in A1: C:UsersfooDocumentsparent.folderfoo.bar.txt To get the file, parent folder and path names use the following formulas which all point to A1. File Names File Name with File Type Result: foo.bar.txt =MID(A1,FIND(“*”,SUBSTITUTE(A1,””,*”””””

## Finding The Last Row Or First And Last Available Blank Row

1 minute read Examples on how to first the last row, first available blank row or the last blank row ‘ last Row, working from the bottom of a Column upwards (therefore the Column can contain blanks & the last Row will still be returned) MsgBox Cells(Rows.Count, 1).End(xlUp).Row MsgBox Cells(Rows.Count, “A”).End(xlUp).Row MsgBox ActiveSheet.Cells(ActiveSheet.Rows.Count, “A”).End(xlUp).Row MsgBox … Read more