## 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 which you can paste to C2 and D2 and drag down: C2: Countif: =COUNTIF(\$A\$2:\$A\$10, B2) D2: Vlookup: =VLOOKUP(B2, \$A\$2:\$A\$10, 1, 0)   A B … Read more

## Create A Named Range And Choose The Scope

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 advantages of scope. ‘================================================================================================== ‘ ## Create a Named Range with Workbook Scope ‘================================================================================================== Sub createNamedRangeWB() ‘// Vars Dim myWorksheet As Worksheet Dim myNamedRange As Range ‘// Declare vars … Read more

## Browse for the Addin Folder

To manually locate the default Excel AddIns folder, follow the steps below. Click the Developer tab on the Excel Ribbon. If it isn’t visible, follow the steps here. Click the Add-Ins command In the Add-Ins window, select any add-in in the list, and click the Browse button. That will open the Browse window, at the … Read more

## Format Numbers As Thousands And Millions

1 minute read When you want to just show numbers as 9.1 M or 830 K ‘================================================================================================== ‘ ## Format Millions with 1 decimal. focuses on constants and formulas ‘================================================================================================== Sub FormatMillions() Dim rng As Range Set rng = Application.Selection If rng.Count = 1 Then rng.Select rng.NumberFormat = “[>=100000]#,##0.0,,”” M””;[=100000]#,##0.0,,”” M””;[=100000]#,##0.0,,”” M””;[=100000]#,##0.0,,”” M””;[=1000]#,##0.0,”” K””;[=1000]#,##0.0,”” K””;[=1000]#,##0.0,”” … Read more

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 macros from the Standard Modules which reset the context menu and then add your buttons each time the workbook is activated. In the … Read more

## Delete Entire Row If No Data Is Found

less than 1 minute read These piece of code lets you delete an entire row within the used range of the sheet if the entire row contains no data. ‘ ====================================================================================================== ‘ ## Deletes the entire row within the used range ‘ if the ENTIRE row contains no data ‘======================================================================================================= Sub DeleteBlankRows() ‘ Vars Dim … Read more

## Inputbox Type 8 Select A Range And Write Values To Each Cell

less than 1 minute read InputBox Type 8 – select a range and write values to each cell ‘================================================================================================== ‘ ## InputBox Example 3: (Type 8) – Inputbox to select a range and write values to each cell ‘================================================================================================== Sub InputBoxChangeCells() ‘// Vars Dim strRng As String Dim rngCell As Range Dim rngSelection As Range … Read more

## Inputbox Type 1 Get A Number From The User

less than 1 minute read InputBox Example 1: (Type 1) – Inputbox to get a number from the user. ‘================================================================================================== ‘ ## InputBox Example 1: (Type 1) – Inputbox to get a number from the user ‘================================================================================================== Sub InputBoxNumber() ‘// Vars Dim AgeNum As Variant ‘// Prompt for an age AgeNum = Application.InputBox _ (Prompt:=”How … Read more

## Convert Numbers Stored As Text When Using A Formula

less than 1 minute read There are a few ways to convert Numbers stored as Text using Formula. You can use: =VALUE() or =NUMBERVALUE() for floating point Numbers. =INT() if you just have whole Numbers =T() to retain formatting. If you have a Number stored as Text in Cell A1 use any of the following: … Read more

## Use Vba To Join Cell Values With Either & Or Concatenate Formula

2 minute read Join multiple string cells using one of 2 options, the code below shows how to choose the two, it boils down to what the user finds easier to read. =CONCATENATE(A1,” “,B1,” “,C1) or =A1&” “&B1&” “&C1 note: Concatenate formula limitations, 255 string limit and & has none The code below uses the … Read more