Author: Oscar Cronquist Article last updated on February 02, 2018

The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.

The picture above shows a macro that changes a few properties of the Range Object.

Statement Syntax

With object
[ code]
End With

The following macro changes the cells in cell range B2:B4 to bold, indents the text and changes the column width to 15.

Sub Macro1()
With Range("B2:B4")
    .Font.Bold = True
    .InsertIndent 1
    .ColumnWidth = 15
End With
End Sub

WITH … END WITH statement examples

test
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 […]
test
Add values to a regular drop-down list programmatically
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]
test
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 […]
test
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 […]
test
Basic data entry [VBA]
In this small tutorial, I am going to show you how to create basic data entry with a small amount […]
test
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 […]
test
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 […]
test
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 subfolders. It […]
test
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 […]
test
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 […]
test
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 […]
test
Count matching strings using regular expressions
This article demonstrates a User Defined Function (UDF) that counts how many times a string exists in a cell value […]
test
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 […]
test
Customize Excel maze
Terry wants to make a different sized maze and I think that is a great idea. Perhaps you remember that I […]
test
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 […]
test
Extract cell references from a formula
This article demonstrates a User Defined Function that allows you to extract cell references from a formula in a given […]
test
Fetching values from ThingSpeak programmatically
  Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm) Thingspeak […]
test
Filter duplicate files in a folder and subfolders
This is a follow up to my last post Compare file names in two different folder locations and their subfolders, the obvious […]
test
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 […]
test
Find and replace strings in file names, folder name and subfolders
The following two macros FindReplace() and Recursive() let you rename files and folders recursively based on a search string. The […]
test
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 press with right […]
test
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 […]
test
How to create a list of comments from a worksheet programmatically
Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]
test
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
test
How to highlight row of the selected cell programmatically
Today I would like to share with you these small event handler procedures that make it easier for you to […]
test
How to save specific multiple worksheets to a pdf file programmatically
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
test
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 […]
test
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 […]
test
How to use the Scroll Bar
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
test
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 […]
test
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 […]
test
Move a shape [VBA]
This article demonstrates how to move a shape, a black arrow in this case, however, you can use whatever shape […]
test
Move data to workbooks
This article demonstrates a VBA macro that saves user input to a given workbook and worksheet programmatically. Macros are great […]
test
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 an […]
test
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 […]
test
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 […]
test
Print screen the entire worksheet
This article demonstartes different techniques on how to take a screenshot of your worksheet. You can do that by simply […]
test
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 […]
test
Search all workbooks in a folder and sub folders
This article demonstrates a macro that allows you to search for a text string in multiple worksheets and workbooks located […]
test
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 […]
test
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 […]
test
Toggle a macro on/off using a button
This article demonstrates how the user can run a macro by press with left mouse button oning on a button, […]
test
Unzip files in folder and subfolders
The VBA macro demonstrated in this article lets you unzip all zip files in a folder to a destination folder […]
test
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 […]
test
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 […]
test
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 […]
test
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 […]

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.