Home / Tag: Excel Tricks

Tag: Excel Tricks

Excel VBA String Functions: SPLIT, JOIN, CONCATENATE

Excel VBA String Functions: SPLIT, JOIN, CONCATENATE Excel VBA Functions to Split a String into Substrings, and Join an Array of Substrings to form a String, with Examples. Also refer Related Link: Excel VBA String Functions: LEFT, RIGHT, MID, LEN, REPLACE, INSTR, INSTRREV —————————————————————– Contents: Split Function (VBA) Join Function (VBA) Concatenate with & (Worksheet...

Excel VBA String Functions: LEFT, RIGHT, MID, LEN, REPLACE, INSTR, INSTRREV

  Excel VBA String Functions:   Excel VBA String Functions for Finding and Replacing Text, with Examples: LEFT, RIGHT, MID, LEN, REPLACE, InStr & InStrRev Functions   ——————————————————- Contents: LEFT Function (Worksheet / VBA) RIGHT Function (Worksheet / VBA) MID Function (Worksheet / VBA) LEN Function (Worksheet / VBA) REPLACE Function (Worksheet) REPLACE Function (VBA)...

Automate Outlook using vba: Sending Email from Excel using Outlook

  Automate Outlook using vba: Sending Email from Excel using Outlook   ———————————————————————————————— Contents: Automate Outlook using vba Send text and also contents from the host workbook’s worksheet range as Mail Body, and add an attachment with the mail, using Early Binding Send the Host Workbook as an attachment with the mail, using Early Binding...

Automate Microsoft Word from Excel, using VBA

Automate Microsoft Word from Excel, using VBA ——————————————————————— Contents: Automating an Office Application Practical Examples of Automating Word from Excel ——————————————————————— Automation is a process by which one application communicates with or controls another application. This can be done in two ways: Early Binding and Late Binding. Binding is a process where you assign an...

Automate Microsoft PowerPoint from Excel, using vba, Run a Slide Show

  Automate Microsoft PowerPoint from Excel, using vba, Run a Slide Show   ————————————————————————————————————— Contents: Automate Microsoft PowerPoint from Excel Create a new PowerPoint ppt of 3 slides with sound effect, and run a slide show, using Early Binding Create a new PowerPoint ppt of 4 slides with sound clips and chart, run & view...

Automate Microsoft Outlook from Excel, using VBA

  Automate Microsoft Outlook from Excel, using VBA   ————————————————————————————————————————————— Contents: Create a new instance of the Outlook application and Create an instance of a NameSpace object variable Reference Existing Outlook Folders and Create New Folders, in Automation Create New Outlook Items and Reference Outlook Items, in Automation —————————————————————————————————————————————   In this section it is...

Refresh Excel Pivot Table and Cache of PivotTable, using VBA

  Refresh Excel Pivot Table and Cache of PivotTable, using VBA   Refresh Data in a PivotTable report: You have different ways to refresh and update data in a PivotTable report with any changes made in the source data. This section explains how to update or refresh a PivotTable report, with VBA code.   Related...

Create & Customize Excel Pivot Table Charts, using VBA

Create & Customize Excel Pivot Table Charts, using VBA Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA 4. Excel Pivot Table Address, Location, Move & Copy using VBA 5....

Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA

Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA This section explains how to access, reference, select or use Pivot Fields and Pivot Items in a Pivot Table, viz. Pivot Field, Column Field, Row Field, Data Field, Page Field, Pivot Items in a Field, Hidden Fields, … Related Links: 1. Create...

Excel Pivot Table Address, Location, Move & Copy using VBA

Excel Pivot Table Address, Location, Move & Copy using VBA This section explains how to get the Address of a PivotTable in a worksheet; Move PivotTable to a new location; Copy a PivotTable and paste as values or paste as a PivotTable. Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing...

Referencing an Excel Pivot Table Range using VBA

Referencing an Excel Pivot Table Range using VBA This section explains how to access, reference, select or use a specific part of a Pivot Table, be it the Field or Data Labels, Row or Column Ranges, the Data or Values Area, the Page Area, specified cells or range in a PivotTable report or the entire...

Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA

Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA 4. Excel Pivot Table Address, Location, Move & Copy using...

Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA

Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA 4. Excel Pivot Table Address, Location, Move &...

Create an Excel Pivot Table report using VBA

Create an Excel Pivot Table report using VBA Related Links: 1. Create an Excel Pivot Table report using VBA 2. Referencing an Excel Pivot Table Range using VBA 3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA 4. Excel Pivot Table Address, Location, Move & Copy using VBA 5. Excel...

200-Year Calendar in Excel VBA, Free Download

  200-Year Calendar in Excel VBA     The Calendar has been created with vba code, in excel. It spans 2 centuries, covering years 1900 to 2100. Select the year from the drop-down list (yellow color cell), and the Calendar will auto-generate for that year, showing all 12 months. See below for a snapshot, and...

Excel VBA Arrays: One-Dimensional & Multi-Dimensional, Dynamic Arrays, ReDim Statement, Lower & Upper Bound, 200-Year Calendar in Excel, ParamArray

Excel VBA Arrays: Declaring Arrays, One-Dimensional and Multi-Dimensional Arrays, Fixed-Size and Dynamic Arrays, ReDim Statement, Lower Bound and Upper Bound, Array Size, Create a 200-Year Calendar with Excel VBA, ParamArray  (Parameter Array) ————————————————————————————————————— Contents: VBA Arrays Declare Arrays, Sizing an Array, Determining the Upper and Lower Bounds One-Dimensional and Multi-Dimensional Arrays Fixed-size and Dynamic Arrays;...

Excel UserForm Controls – CheckBox, OptionButton and ToggleButton

  UserForm Controls – CheckBox, OptionButton and ToggleButton     UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects....

Excel UserForm Controls – ComboBox and ListBox: AddItem Method, BoundColumn, List Property, ListIndex, RowSource, Selected Property, …

  UserForm Controls – ComboBox and ListBox   ———————————————————- Contents: Difference between ListBox and ComboBox Key Properties of ComboBox and ListBox Add Items/Data to (Populate) a ListBox or ComboBox Extract ListBox & ComboBox Items, with VBA Delete ListBox rows using the RemoveItem Method ———————————————————-   UserForm acts as a container in which you add multiple...

Excel UserForm Controls – Label, TextBox and CommandButton

UserForm Controls – Label, TextBox and CommandButton UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects. Using ActiveX Controls...

Excel UserForm and Controls – Properties; Setting control properties at design-time or run-time.

  UserForm and Controls – Properties   ———————————————————————- Contents: UserForm and Controls Basic Properties common to the UserForm and most Controls Other Properties common to the UserForm and most Controls Specifying Color in Properties Window Applicability of Properties to UserForm and its Controls – A SnapShot ———————————————————————-   UserForm acts as a container in which...

UserForms in Excel VBA – UserForm Basics; Add Controls dynamically at run-time using the Add Method; UserForm Controls (A Snapshot)

UserForms in Excel VBA – UserForm Basics; Add Controls dynamically at run-time using the Add Method; UserForm Controls (A Snapshot)  ———————————————————————– Contents: Excel UserForms Create a UserForm UserForm Controls – A SnapShot UserForm Basics Add Controls to a UserForm ———————————————————————– Note: In below given examples, vba codes are required to be entered in the Code...

Conditional Statements in Excel VBA – Select…Case Statement (VBA)

Conditional Statements in Excel VBA Related Link: If…Then…Else Statements (VBA). There are primarily two Conditional Statements in VBA: If…Then…Else Statements and Select…Case Statement. In both of these, one or more conditions are evaluated and a block of code is executed depending on the result of the evaluation. Select…Case Statement (VBA) If…Then…Else Statements (VBA) ———————————————————————————————– Contents:...

Conditional Statements in Excel VBA – If…Then…Else Statements (VBA)

Conditional Statements in Excel VBA Related Links: 1. Excel IF Function and IF Statements. 2. Select…Case Statement (VBA). There are primarily two Conditional Statements in VBA: If…Then…Else Statements and Select…Case Statement. In both of these, one or more conditions are evaluated and a block of code is executed depending on the result of the evaluation....

Excel VBA Loops, with examples. For Loop; Do While Loop; Do Until Loop.

Excel VBA Loops, with examples. For Loop; Do While Loop; Do Until Loop. ——————————————————————————————– Contents: The For Loop The For … Next Statements The For Each … Next Statements The Do While Loop The Do While … Loop Statements; The Do … Loop While Statements The Exit Do Statement The Do Until Loop The Do...

Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value

Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup with VLookup Excel function. 3. Left Lookup, with Index, Match & Offset Excel functions. 4. Case Sensitive Vlookup; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value. Return MULTIPLE...

Excel Text and String Functions: TRIM & CLEAN

Excel Text and String Functions: Removing spaces and nonprinting characters from text: Excel TRIM & CLEAN functions Related Links: 1. ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code. 2. Excel CODE & CHAR functions. 3. Excel Text and String Functions: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE. When you import data into...

Run VBA Macros on Protected Worksheet; Unprotect & Protect, UserInterfaceOnly argument; Worksheet.Protect Method

  Run VBA Macros on Protected Worksheet   ——————————————————————————————-  Contents: Unprotect & Protect Error Handler UserInterfaceOnly Run macro on protected worksheet, code remaining visible & editable, but protection password hidden Using the UserInterfaceOnly argument to Enable Auto Filter Using UserInterfaceOnly argument to Allow Grouping Worksheet.Protect Method ——————————————————————————————-    If you run macros on a protected...

ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code.

ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code Related Links: 1. VBA Chr & Asc functions explained; corresponding Excel CHAR and CODE functions. 2. Excel Text and String Functions: TRIM & CLEAN. A ‘character set‘ maps characters to their identifying code values. Unicode is a global standard for character encoding and is the...

Excel CODE & CHAR Functions, VBA Asc & Chr Functions

Excel Text and String Functions: Excel CODE & CHAR Functions, VBA Asc & Chr Functions, with examples. Related Links: 1. Using VBA Chr and Asc functions to convert excel column number to corresponding column letter, and column letter to column number. 2. ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code. 3. Excel Text...

Excel VBA – Convert Excel Column Number to Column Letter, and Column Letter to Column Number

  Excel VBA – Convert Excel Column Number to Column Letter, and Column Letter to Column Number   Related Links: VBA Chr & Asc functions explained; corresponding Excel CHAR and CODE functions.   ——————————————————————————————-   Contents: VBA Codes to Convert Excel Column Number to corresponding Column Letter VBA Codes to Convert Excel Column Letter to corresponding...

Excel Text and String Functions: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE

Excel Text and String Functions:   Excel Functions for Finding and Replacing Text, with Examples: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE Related Links: 1. Excel Text and String Functions: TRIM & CLEAN. 2. Excel CODE & CHAR functions. ————————————————————————————————————————- This section deals with the Excel Worksheet String Functions – click here for detailed...

Find Method in Excel VBA, Find Multiple Occurrences, Find Method to do Vlookup, Find Method to Search Date

Find Method in Excel VBA, Find Multiple Occurrences, Use Find Method to do Vlookup, Find Method to Search Date ———————————————————————— Contents: Find Method in Excel VBA Find multiple occurrences of an item or value in a range Using Find Method to do VLookUp Using Excel Find Method to Search for a Date ———————————————————————— Find Method...

Worksheet Selection Change Event, Excel VBA

  Worksheet Selection Change Event in Excel VBA and Preventing Event Loops   Related Links: Excel VBA Events, Event Handler, Trigger a VBA Macro. Worksheet Change Event in VBA and Preventing Event Loops.   ————————————————————————    Contents: Worksheet_SelectionChange Event Preventing Event Loops with Application.EnableEvents = False ————————————————————————      Worksheet_SelectionChange Event:   You can auto...

Worksheet Change Event, Excel VBA

  Worksheet Change Event in VBA and Preventing Event Loops   Related Links:  Excel VBA Events, Event Handlers, Trigger a VBA Macro. Worksheet Selection Change Event, Excel VBA.   ————————————————————————    Contents: Worksheet_Change Event Preventing Event Loops with Application.EnableEvents = False ————————————————————————      Worksheet_Change Event:   You can auto run a VBA code, when...

Excel VBA Events, Event Procedures (Handlers), Triggering a VBA Macro

Excel VBA Events, Event Procedures (Handlers), Triggering a VBA Macro Related Links:   Worksheet event – Change Event – is illustrated in chapter: Worksheet Change Event in VBA and Preventing Event Loops;   Worksheet event – Selection Change Event – is illustrated in chapter: Worksheet Selection Change Event, Excel VBA;   Event Procedures of ActiveX...

Excel Date and Time Functions and Formulas

  Excel Date and Time Functions & Formulas     Excel Dates Equate to Serial Numbers:   Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system....

CHOOSE Function in Excel

  CHOOSE Function in Excel and its uses, make Vlookup Look Left & Find Date of Next Monday with CHOOSE function. Related Links: VLOOKUP formula that looks left instead of right. ————————————————————————————————————————– Excel CHOOSE Function The CHOOSE function returns a value from a list of values based on a specified position number.   Syntax: CHOOSE(position_number,...

Find Smallest and Largest Value in a Range with VBA

Finding Smallest and Largest Value with VBA. Using ParamArray to find Minimum Value. Related Links: Remove Duplicates in a range, using “Find ‘Smallest’, ‘Largest’, ‘K-th Smallest’ and ‘K-th Largest’ Numbers in a Range, with Excel Functions. ———————————————————————————————– Contents: Determine smallest value in range Determine largest value in range Determine smallest value in each non-blank row...

Find ‘Smallest’, ‘Largest’, ‘K-th Smallest’ and ‘K-th Largest’ Numbers in a Range

Find ‘Smallest’, ‘Largest’, ‘K-th Smallest’ and ‘K-th Largest’ Numbers in a Range with Excel Functions   Related Links: Remove Duplicates in a range, using “Find Smallest and Largest Value with VBA. Using ParamArray to find Minimum Value.   —————————————————————————————————————————  Find Smallest Number or K-th Smallest Number in a Range Find Smallest Number in a Range,...

Excel IF Function and IF Statements

  Excel IF Function and IF Statements, with Examples     Related Link: If…Then…Else Statements (VBA).   ————————————————————————————————————————   Excel IF Function: The IF function is used to test if a condition is True or False. If a specified condition evaluates to TRUE, the IF function returns one value, and if the condition evaluates to...

Excel VLOOKUP Function

  VLOOKP Function in Excel (with examples), using Exact and Approximate Matches, Error (#N/A) handling, …     Related Links: 1. Left Lookup with VLookup Excel function. 2. Left Lookup, with Index, Match & Offset Excel Functions. 3. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value. 4. Case Sensitive Vlookup; Finding...

Excel OFFSET Function

  Using the Excel OFFSET Function, with Examples   ——————————————————————————————————————————   OFFSET Function: The excel OFFSET function returns a range, which is offset by a specified number of rows and columns from a reference range.   Syntax: OFFSET(reference_range, rows, columns, height, width)   1. reference_range is a single cell or a range of adjacent cells...

Remove Duplicates in a range, using “Remove duplicates” button in Data Tools

  Related Links: Count Number of Unique Values, with Excel Functions Remove Duplicates or Create a List of Unique Records Using Advanced Data Filter Remove Duplicates or Create a List of Unique Records using Excel Formula —————————————————————————————————————————   Remove Duplicates in a range, using “Remove duplicates” button in the top menu tab Data -> Data...

Remove Duplicates or Create a List of Unique Records using Excel Formula

  Related Links: Count Number of Unique Values, with Excel Functions Remove Duplicates or Create a List of Unique Records Using Advanced Data Filter Remove Duplicates in a range, using “Remove duplicates” button in Data Tools ————————————————————————————————————————–     Remove Duplicates or Create a List of Unique Records in Excel     Create a List of...

Remove Duplicates or Create a List of Unique Records using Advanced Data Filter

  Related Links: Count Number of Unique Values, with Excel Functions Remove Duplicates or Create a List of Unique Records using Excel Formula Remove Duplicates in a range, using “Remove duplicates” button in Data Tools     Create a List of Unique Records Using Advanced Data Filter: ——————————————————————————————————– Using Advanced Data Filter   Select a...

Count Number of Unique Values in a Range (with Excel Functions)

  Related Links: Remove Duplicates or Create a List of Unique Records Using Advanced Data Filter Remove Duplicates or Create a List of Unique Records using Excel Formula Remove Duplicates in a range, using “Remove duplicates” button in Data Tools —————————————————————————————————————————   Count Number of Unique Values (when No Blank Cells), with Excel Functions:  ...

Excel INDIRECT Function

  Excel INDIRECT Function, with Examples:   —————————————————————————————————————————–   Excel INDIRECT Function: The INDIRECT function returns the result of a specified reference.   Syntax: INDIRECT(text_reference, reference_type)   1. text_reference is a reference to a cell and that cell can contain: (i) an A1-style reference; (ii) an R1C1-style reference; (iii) a named range;  or (iv) a...

Convert Text and Cells with Formulas to UpperCase with VBA

  Convert to Uppercase – Excel VBA Codes:   To insert vba code for worksheet event procedures, right click on the Worksheet name tab, select View Code and paste the code into the Code Window that appears at right.   Convert Text and Cells with Formulas to UpperCase – worksheet event procedure   Private Sub...

Change Font Color in Cells with VBA

  VBA Code for customized Font Color in Cells Code for different font color in cells having “formulas”, “formulas and numbers” and “constants”.     Sub FontColorFormulas1() ‘Worksheet Code for different font color in cells having formulas, formulas and numbers and constants. Run this code to change font colors in Used Range in Active Sheet.Dim...

VBA codes to determine Last Used Row & Last Used Column in Excel

  VBA Codes – Find last used row in a worksheet or in a specific column; Find last used column in a worksheet or in a specific row.   Related Links: Find last used row number & cell address with excel functions.   ———————————————————————————————————– Contents: Use End(xlUp) to determine Last Row with Data, in one...

Shade Alternate Rows – Conditional Formatting

  Shade Alternate Rows using Conditional Formatting   —————————————————————————————————————————-   Shade Alternate Rows, including blank/unused range   Select worksheet range you wish to format   Home  ->  Conditional Formatting -> New Rule -> select “Use a formula to determine which cells to format” option (Excel 2007)   Format -> Conditional Formatting  ->  “Formula Is”  (earlier...

Excel SUMPRODUCT Function

  Using SUMPRODUCT FUNCTION Using  double unary minus (–)  n SUMPRODUCT Function -> A significant use   ————————————————————————————————————————————————————   Excel SUMPRODUCT Function:   The SUMPRODUCT function multiplies corresponding items of two or more arrays and then sums up these products.   Syntax: SUMPRODUCT(array1, array2, … array255)   1. array1, array2, … array255 are arrays whose...

Left Lookup with Vlookup Excel Function

  VLOOKUP formula that looks Left instead of Right   Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup, with Index, Match & Offset Excel functions 3. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value. 4. Case Sensitive Vlookup; Finding the 1st, 2nd, nth or last occurrence of the...

Left Lookup with Index, Match and Offset Excel Functions

  Left Lookup with Index, Match and Offset Excel Functions   Related Links: 1. Excel VLOOKP Function, with examples. 2. Left Lookup with VLookup Excel function 3. Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value. 4. Case Sensitive Vlookup; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value....

Last used row number and cell address, last numeric or text value in column

  Find Last Used Row in a Column:   Related Links: VBA Codes – Last used row; Last used column.   ———————————————————————————————————     Formula for ‘Cell Address of the last numeric value within column’   =ADDRESS(MATCH(9.99999999999999E+307, A:A), COLUMN(A1))  [Formula]   The formula returns cell address of the last numeric value in column, which is...

Scroll to top