VBA


Excel » VBA »

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 […]

How to replace part of formula in all cells

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]

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 (VBA)

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 […]

How to use DIALOG BOXES

A dialog box is an excellent alternative to a userform, they are built-in to vba and can save you time because […]

Working with ARRAY VARIABLES

This post will teach you how to work with excel arrays in visual basic for applications (VBA). Why do you […]

Resize a range of values

The user defined function demonstrated below, resizes a range you specify to columns or rows you also specify. The first […]

How to enter an array formula

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 […]

Split text across columns

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 […]

List comments [VBA]

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 […]

Move data to workbooks

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 […]

Customize Excel maze

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]

What's on this page Insert a text box Font and font size Add or edit text Position a text box […]

Show and hide a picture [VBA]

Have you ever seen dashboards where you can click a shape and a picture shows up. If you click it […]

Click a button to make specific worksheets hidden or visible (vba)

Click button "Show / Hide worksheets" to show or hide worksheets entered in cell range B6:B7. Instructions Here is how […]

Click a cell to make a column hidden or visible [VBA]

Click a single cell in column D to hide or show the comments in column E. Selecting multiple cells won´t […]

Scroll through a data set [VBA]

Sometimes you just want to show a small section of your data set, like in a dashboard or a chart. […]

Count cells by cell and font color

Dave asks: How difficult would it be to make it count colour alone (so not unique values) and / or […]

Count unique distinct values by cell color

PRASHANT asks: i need to count unique number in a specific coloured cell for eg if there are coloured cells […]

Highlight date ranges overlapping selected record [VBA]

The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional […]

Find out if excel files in a folder are password protected

rahul jadhav asks: How can we identify any protected excel file before opening using macro VBA (e.g. i have a […]

Count text strings in formulas [VBA]

Rahul Jadhav asks: I have many excel files with multiple sheets and each excel sheet has many formula which are […]

Working with FILES

What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]

Change picture [VBA]

Rahul asks: i want to know that when we create a vlookup sheet, and in the name column we enter […]

Substitute multiple text strings [UDF]

The user defined function demonstrated below replaces multiple old text strings with new text strings in a cell. The custom […]

Move a shape [VBA]

This post shows you how to move a shape with vba code. Select a cell (button name) and the arrow […]

Lookup multiple values in one cell [UDF]

Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]

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 multiple […]

Hide specific columns [VBA]

Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am […]

Copy excel table filter criteria [VBA]

Here is how to copy filter criteria from an excel table and use the same table filters on another table. […]

Excel calendar [VBA]

This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day […]

Use filtered table values in a drop down list [VBA]

I read a very interesting blog post about Using Custom Functions in Dynamic Ranges Gabhan Berry creates a user defined […]

Sort values in a cell using a custom delimiter [VBA]

The following macro lets you select a cell range and a delimiting character. The macro sorts the values in each […]

Create links to all sheets in a workbook

The macro demonstrated below creates hyperlinks to all worksheets in the current worksheet.  You will then be able to quickly […]

Quickly create new sheets [VBA]

The following macro let´s you select a cell range and then the macro creates sheets with the same names as […]

Insert hyperlinks to all files in current folder

The macro creates a new sheet. Inserts all filenames in current folder as hyperlinks except the current workbook. VBA Code […]

Interactive Excel chart [VBA]

I found a basic interactive chart on the chitika website and my first thought was if I could do this […]

Sort values in an Excel table [VBA]

awall asks: Hey, can you do the opposite of this - not random order but this is my situation. I […]

Search two related tables simultaneously [VBA]

Let´s say you do a lot of searches in two tables. The tables are related so it would be great […]

Learn how to return values depending on how you enter the UDF

In this vba tutorial I am going to show you how to return values from an udf, depending on where […]

Select a cell in a table and the chart updates automatically [VBA]

The following vba code makes it possible to automatically update a chart when you click a cell in a table. […]

Excel template: Getting Things Done [VBA]

In this blog post I am going to demonstrate a simple workbook where you can create or delete projects and […]

Normalize data [VBA]

Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes […]

Add values to different sheets [VBA]

Phil asks: Could you please show me the code to place the copied data into a different tab instead of […]

Add values to a table [VBA]

Cyril asks: I do remember seeing one nice way of populating a table with the use of vba such as […]

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The […]

Copy table design settings [VBA]

The macro provided in this blog post let´s you copy table design settings to other tables in the same workbook. […]

Toggle a macro on/off using a button

This post demonstrates using a single button (form control) to turn a macro on or off. VBA Macro I am […]

Automate data entry [VBA]

In some cases it can be useful and timesaving to automate data entering. The vba examples here all enter a […]

Apply data validation lists dynamically [VBA]

Table of Contents Applying Drop Down lists dynamically using an Excel Defined table Applying Drop Down lists dynamically (vba) Add […]

Workbook log [VBA]

This post demonstrates how to automatically create log entries when a workbook opens or closes. VBA code Where to copy […]

Copy a cell range whose size is likely to change from time to time [VBA]

In this blog post I will demonstrate some vba copying techniques. Example 1, This example code copies cell range A1:B2 […]

List all open workbooks and corresponding sheets [VBA]

In this post I am going to demonstrate how to create a new sheet in the current workbook and list […]

Basic data entry [VBA]

In this small tutorial I am going to show you how to create basic data entry with a small amount […]

List all tables and corresponding headers in a workbook [VBA]

This macro creates a new sheet and lists all tables and corresponding table headers in a workbook. Example Sheet1, 2 […]

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]

This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) […]

Filter an Excel table using the selection change event [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 table [udf and array 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 fill a combox with table headers Populate a […]

Copy selected rows (checkboxes) (2/2)

This post describes how to copy selected rows using checkboxes. In a previous post I showed you how to add/remove […]

Add checkboxes to a sheet (1/2) [VBA]

In this post I will demonstrate how to create checkboxes in nonempty rows. VBA code   Where to copy vba […]

Add values to a data validation list [VBA]

In this tutorial I am going to show you how to create a drop down list (data validation) in cell […]

Reorganize data [UDF]

Sean asks: Sheet1 A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country […]

Populate a list box with visible unique values from an Excel table [VBA]

Excel tables, introduced in excel 2007, sort, filter and organize data any way you like. You can also format data […]

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 post describes how to lookup values in two dimensions with multiple tables using a User Defined Function. You can […]

Create a custom-made item on the shortcut menu [VBA]

This post describes how to add a new custom-built item to the shortcut menu in excel 2007. In excel 2007 […]

Sumif across multiple sheets [UDF]

This post describes a basic user defined function that searches multiple ranges and adds corresponding values.         […]

Select cell A1 on all sheets before you close a workbook [VBA]

This post demonstrates how to automatically select cell A1 on each sheet in a workbook before you close a workbook. […]

Identify duplicate files in excel

Yesterday I demonstrated how to create a list of files in a folder and subfolders. Today I am going to […]

List files in a folder and subfolders [UDF]

This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]

Filter unique distinct records (case sensitive)

The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. How to use the […]

Filter unique distinct values (case sensitive) [Excel UDF]

The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]

Combine cell ranges eliminating blanks

This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes […]

Word frequency [UDF]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Fuzzy lookups [UDF]

In this post I will describe a basic user defined function with better search functionality than the array formula in […]

Filter emails from an excel range [UDF]

This udf extracts all words containing a specified string. Example, Cell range B1:M50 contains random sentences. I have inserted some […]

Find positive and negative amounts that net to zero [UDF]

This article describes a UDF that finds positive and negative numbers that net to approximately zero. You specify the range […]

Find numbers in sum [UDF]

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. VBA code: How to implement user defined function in […]

List permutations with repetition [UDF]

This blog post describes how to create permutations. Repetition is allowed. Vba code: Function ListPermut(num As Integer) 'Permutations with repetition […]

Use text qualifiers to make text to columns conversion easier [VBA]

This blog post describes how to create text qualifers and make "text to columns" conversion easier. Example I copied a […]

Create a Print button [VBA]

This article describes how to create a button and place it on an Excel worksheet. Then assign a macro to […]

Select and view invoice [VBA]

A previous post described how to create a listbox filled with unique invoice numbers: Excel vba: Populate listbox with unique invoice […]

Populate listbox with unique invoice numbers [VBA]

This post describes how to fill a listbox with unique values. Create button (Form Control) Select sheet "Invoice" Click "Developer […]

Edit invoice data [VBA]

In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]

Save invoice data [VBA]

This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows […]

Filter values existing only in one out of two ranges [UDF]

This post describes how to filter values existing only in one out of two cell ranges. See picture. This udf […]

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 […]

Select value based on time and date [VBA]

Introduction This blog post describes how to automatically select a value based on time and date using vba in excel. […]

Filter duplicates in a large dataset [UDF]

Overview This blog post shows you how to filter duplicate values in a large list. We are going to create […]

Count unique distinct values in a large dataset [UDF]

Overview This blog post describes how to count unique distinct values in list. We have created excel formulas before to […]

Remove duplicates from a large dataset [UDF]

Aamer asks: I have a sheet with 3000 rows of invoice dates that are out of order. There could be […]

Filter unique words from a range [UDF]

Overview This blog post describes how to create a list of unique words from a cell range. Unique words are […]

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 […]

Consolidate sheets [vba]

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]

Categorize values into multiple columns [VBA]

I am fairly new to vba and I am amazed of how much you can automate in excel. In this […]

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

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Array Formula in D6 =SMALL(IF(ISERROR(MATCH($E$2+ROW(OFFSET($B$2, 0,0, […]

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, […]

Sum cells with check boxes

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. […]