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

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