VBA
Excel » VBA »
Extract text between words [UDF]
Blake asks: I have a somewhat related question, if you don't mind: I have very large amount of text in […]
The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that […]
How to use the EVALUATE method
The Evaluate method converts an Excel name to an object or value. The picture above demonstrates a macro that uses […]
How to use the GOTO statement [VBA]
The image above demonstrates the GoTo statement. It "jumps" or "navigates" to another place in the code, in this case, "Start". […]
How to use the ARRAY function [VBA]
The ARRAY function creates a Variant variable containing array values. The macro above populates variable MyArray with values Cat, Dog […]
How to use the LBOUND and UBOUND functions
The Lbound and Ubound functions calculate the size of of an array. The Lbound returns the lower limit of an array and […]
How to use the JOIN function [VBA]
The JOIN function concatenates a set of substrings in an array, the image shows a user-defined function entered in cell […]
How to use the RANGE.OFFSET property
The OFFSET property returns a Range object based on a row and column number. The image above shows a macro […]
How to use the SGN function [VBA]
The SGN function returns an integer that shows the sign of the number. Argument SGN returns Number greater than 0 (zero). […]
How to use the FIX function [VBA]
The FIX function removes the decimals from the argument. Excel Function VBA Syntax Fix(number) Arguments number Required. Is a Double or […]
How to use the INT function [VBA]
The INT function removes the decimals if the numeric value is above 0 (zero) and returns a negative integer less than […]
How to use the RANGE.FIND method
The Range.Find method returns a range object for the first cell that matches the lookup value. Macro used in workbook above The […]
Copy data from workbooks in folder and subfolders
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]
How to copy non contiguous cell ranges
If you try to copy multiple cell ranges on a worksheet that don't have the same number of rows or […]
Search for a file in folder and subfolders [UDF]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
How to use the SPLIT function [VBA]
The picture above shows a user-defined function (UDF) that splits the string in cell B3 using delimiting character "|". The SPLIT function […]
How to quickly select blank cells
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]
How to add a macro to your Excel Quick Access Toolbar
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]
How to use the WITH … END WITH statement
The With ... End With statement allows you to write shorter code by referring to an object only once instead […]
How to use the DO LOOP statement
The Do Loop statement allows you to repeat specific code until a condition is met. There are two possible ways […]
How to use SELECT CASE statement
The SELECT CASE statement allows you to compare an expression to multiple values. It is similar to the IF THEN ELSE […]
How to use the FOR NEXT statement
The picture above demonstrates a FOR NEXT statement that goes through each cell value in cell range B3:B8 and adds it to […]
How to use the IF THEN ELSE ELSEIF END IF statement [VBA]
The picture above demonstrates an IF statement that checks if the value in B3 is smaller than the value in […]
Opening a workbook runs a macro automatically
This article explains how to set up a workbook so a macro is executed every time you open the workbook. […]
Count matching strings using regular expressions
A regular expression is a pattern containing specific characters to search for sub-strings in strings. The patterns are at first […]
Split search value using delimiter and search for each substring
Anil asks: I have A1(anil singh raj) It can be anything Like A1(singh raj anil) I want return value in […]
Fetching values from ThingSpeak using vba
Thingspeak is web service that allows you to upload and store data from IoT devices. Picture above shows cheap chinese […]
Extract cell references from a formula
I am trying to build a regular expression that matches cell references in a formula. A regular expression is a […]
Working with the LIKE OPERATOR
The LIKE operator allows you to match a string to a pattern in excel vba. The following characters are specifically […]
A dialog box is an excellent alternative to a userform, they are built-in to vba and can save you time because […]
This post will teach you how to work with excel arrays in visual basic for applications (VBA). Why do you […]
The user defined function demonstrated in the animated ggif below, resizes a range you specify to columns or rows you also […]
Array formulas allows you to do advanced calculations not possible with regular formulas.
Find and replace a text string in file names, folder name and subfolders
The following two macros lets you rename files and folders recursively. Press Alt + F8 to open a list of macros, run […]
Unzip files in folder and subfolders
This vba macro lets you search for zip files in a folder. Then unzip those files to a folder you specify. […]
Working with LIST BOXES (Form Controls)
This blog post shows you how to manipulate List Boxes (form controls) manually and with vba code. The list box […]
List all hyperlinks in worksheet
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]
Filter duplicate files in a folder and sub folders
This is a follow up to my last post Compare file names in two different folder locations and their sub folders, the […]
Compare file names in two different folder locations and their sub folders
Today I would like to share a macro that compares the content in two different folders and their sub folders. […]
How to enter array formulas in merged cells
Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: […]
Find the longest/smallest consecutive sequence of a value [VBA]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show […]
The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]
Count comma separated values [UDF]
I received an email from one of my seven blog readers. In Excel, I have a column, say A, with some […]
Search all workbooks in a folder and sub folders
Search all workbooks in a folder is a popular post, I am happy so many find it useful. rusl cato […]
Did you know that you can select all comments in the current sheet? Press F5, click "Special..." button, select "Comments" […]
Copy filtered Excel tables [VBA]
Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is […]
Highlight row and column of selected cell
Today I would like to share with you these small event handler procedures that make it easier for you to […]
Finding the shortest path – A * pathfinding
Two months ago I posted some interesting stuff I found: Shortest path. Let me explain, someone created a workbook that calculated […]
Macros are great for doing repetitive tasks. Two years ago I wrote a post about transferring data to worksheets. It is […]
Print screen the entire worksheet
I recently discovered something quite useful. Allow me to explain, sometimes you need to take a screenshot of your worksheet […]
Terry wants to make a different sized maze and I think that is a great idea. Perhaps you remember that I […]
Multi-level To-Do list template
Today I will share a To-do list excel template with you. You can add text to the sheet and excel creates […]
Search all workbooks in a folder
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
Add your personal Excel Macros to the ribbon
If you find yourself using the same macros over and over again, you can create a personal *.xlsb file that […]
Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]
Working with TEXT BOXES [Form Controls]
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]
Show / hide a picture using a button
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
Hide specific worksheets programmatically
This article demonstrates techniques to hide and unhide worksheets programmatically. The image above shows the Excel window and the worksheet […]
Click a cell to make a column hidden or visible [VBA]
What's on this page Click a specific cell to hide/show entire column Where to put the event code Download Excel […]
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
Count unique distinct values by cell color
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
Highlight date ranges overlapping selected record [VBA]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
Which Excel files in folder are password protected?
This article explains how to check if Excel files in a given folder are password protected. The image above shows […]
Count text string in all formulas in a worksheet [VBA]
I will in this article demonstrate a macro that counts how many times a specific text string is found in […]
This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
How to change a picture in a worksheet dynamically [VBA]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
Substitute multiple text strings [UDF]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
This article demonstrates how to move a shape, a black arrow in this case, however, you can use whatever shape […]
Lookup multiple values in one cell [UDF]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a Drop Down list. The Drop Down list contains two […]
Hide specific columns programmatically
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
Copy worksheets in active workbook to new workbooks
This article demonstrates several VBA macros, they will save you time if you have lots of worksheets. The first macro […]
Copy Excel Table filter criteria programmatically
I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Extract cell references populated with values [VBA]
This article demonstrates a macro that returns cell references for cell ranges populated with values on a worksheet. Jinesh asks: […]
Sort values in a cell based on a delimiting character [VBA]
This article demonstrates a macro that allows you to sort delimited data in a cell or cell range from A […]
Create links to all sheets in a workbook
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
Create new worksheets programmatically based on values in a cell range [VBA]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
List files in folder and create hyperlinks (VBA)
This article demonstrates a macro that populates a new worksheet with filenames from the active folder which is the same […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
Sort values in an Excel table programmatically [VBA]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]
Prepare data for Pivot Table – How to split concatenated values?
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
Search two related tables [VBA]
This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]
Create comment if cell value is larger than column
It can sometimes be helpful having a large cell value in a comment. You can then easily hover over cell […]
Learn how to return values based on where you enter the UDF
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
Change chart series by clicking on data [VBA]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Excel template: Getting Things Done [VBA]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Add values to worksheets based on a condition [VBA]
This tutorial shows you how to add a record to a particular worksheet based on a condition, the image above […]
Add values to a two-dimensional table based on conditions [VBA]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Toggle a macro on/off using a button
This article demonstrates how the user can run a macro by clicking on a button, the text on the button […]
This article demonstrates how to automatically enter data in cells if an adjacent cell is populated using VBA code. In […]
Apply drop-down lists dynamically
This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]
How to log when a workbook is opened and closed [VBA]
This article demonstrates how to automatically create log entries when a workbook opens or closes using event code. Column A […]
Auto resize columns as you type
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
Copy a dynamic cell range [VBA]
In this blog post, I will demonstrate some VBA copying techniques that may be useful if you don't know the […]
List all open workbooks and corresponding sheets [VBA]
In this post, I am going to demonstrate how to automatically create a new sheet in the current workbook and […]
In this small tutorial, I am going to show you how to create basic data entry with a small amount […]
List Excel defined Tables in a workbook [VBA]
The following macro inserts a new sheet to your workbook and lists all Excel defined Tables and corresponding Table headers […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
Filter an Excel defined Table based on selected cell [VBA]
In this post I am going to demonstrate how to quickly apply a filter to a table. I am using […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
Populate a combobox with values from a pivot table [VBA]
In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]
Copy selected rows (checkboxes) (2/2)
This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]
Add checkboxes to a sheet (1/2) [VBA]
In this post, I will demonstrate a macro that creates checkboxes in nonempty rows, see checkboxes in column E in image […]
Add values to a regular drop-down list programmatically [VBA]
In this tutorial I am going to show you how to add values to drop down list in cell C2. This […]
Filter an Excel defined Table programmatically [VBA]
In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]
Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]
Populate a list box with unique distinct values from a filtered Excel table [VBA]
Excel defined Tables, introduced in Excel 2007, sort, filter and organize data any way you like. You can also format […]
Populate a combo box (form control) [VBA]
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]
Two-way lookup using multiple tables [UDF]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
How to add a custom-made item to the shortcut menu [VBA]
This post describes how to add a new custom-built item to the shortcut menu in Excel, when you right-click a cell […]
SUMIF across multiple sheets [UDF]
This post describes a User Defined Function that searches multiple ranges and adds corresponding values across worksheets in a workbook. A […]
Select cell A1 on all sheets before you close a workbook [VBA]
This post demonstrates a macro that automatically selects cell A1 on each sheet right before you close a workbook. The […]
List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
How to save custom functions and macros to an Add-In
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
Filter unique distinct records (case sensitive) [UDF]
The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record […]
Filter unique distinct values (case sensitive) [UDF]
The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]
Combine cell ranges ignore blank cells
The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
Filter words containing a given string in a cell range [UDF]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]
Find positive and negative amounts that net to zero [UDF]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
List permutations without repetition [UDF]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
List permutations with repetition [UDF]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
Use text qualifiers to make text to columns conversion easier [VBA]
This blog post describes how to insert qualifers to make "text to columns" conversion easier. Example I copied a table from […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
This post demonstrates how to view saved invoices based on the invoice number using a userform. The userform appears when the […]
Populate listbox with unique distinct values [VBA]
This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]
In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Filter not shared values out of two cell ranges [UDF]
This post describes a custom function (User defined Function) that extract values existing only in one out of two cell […]
Filter values in common between two cell ranges [UDF]
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]
Filter duplicates in a large dataset [UDF]
This article demonstrates a user defined function that extracts duplicate values and also count duplicates. Example, the image below shows a list containing […]
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Count unique distinct values in a large dataset [UDF]
This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one […]
Filter unique words from a range [UDF]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
Filter unique distinct words from a cell range [UDF]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
Filter duplicate words from a cell range [UDF]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
Split words in a cell range into a cell each [UDF]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]
Rearrange values based on category [VBA]
In this post I am going to rearrange values from a list into unique columns. Before: After: The code Download […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Calendar with scheduling [vba]
Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]
Create a unique distinct list using Advanced Filter in a macro [VBA]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]
Print consecutive page numbers across multiple worksheets
This article explains how to print multiple worksheets so the page numbers are in sequence across all selected worksheets. Instructions: Go […]
Repeat headers on every page you print
If you want each page to have the same row(s) or column(s) to be repeated at every page you print. […]