Loop Through a Range of Cells

One must-have VBA skill is the ability to loop (or enumerate) through a range of cells. If you do any serious macro work in Excel, you will soon encounter the need to go through a range of cells one by one and perform some action. This basic macro shows you…

Creating, Selecting and Formatting Named Ranges Via VBA

Creating Named Ranges Creating a named range via VBA is much less involved. You can directly define the Name property of the Range object: Sheets(“Sheet3”).Range(“A1:D10”).Name = “MyData” Selecting Named Ranges You simply pass the name of the range through the Range object. This allows you to select the range: Range(“MyData”).Select…

Formatting a Range of Cells In Excel VBA

Formatting Cells Number General Range(“A1”).NumberFormat = “General” Number Range(“A1”).NumberFormat = “0.00” Currency Range(“A1”).NumberFormat = “$#,##0.00” Accounting Range(“A1”).NumberFormat = “_($* #,##0.00_);_($* (#,##0.00);_($* “”-“”??_);_(@_)” Date Range(“A1”).NumberFormat = “yyyy-mm-dd;@” Time Range(“A1”).NumberFormat = “h:mm:ss AM/PM;@” Percentage Range(“A1”).NumberFormat = “0.00%” Fraction Range(“A1”).NumberFormat = “# ?/?” Scientific Range(“A1”).NumberFormat = “0.00E+00” Text Range(“A1”).NumberFormat = “@” Special Range(“A1”).NumberFormat…

Select a Range In Excel VBA

One of the basic things you need to do in Excel VBA is to select a specific range to do something with it. This article will show you how to use Range, Cells, Offset and Resize to select a range in Excel VBA. Select all the cells of a worksheet…

Highlight the Active Row and Column Use Excel VBA

When you looking at a large worksheet with numerous data, it would be nice if Excel automatically highlighted the active cell row and column so that you can easily read the data to avoid misreading them. See following screenshots: The following VBA code examples show ways to highlight the active…

Zoom In and Out in Worksheet on Double-Click

Sometimes, if you want to zoom in and out in a worksheet, you need to have a finger on CTRL and the other on the mouse, you can scroll the wheel on the mouse while pressing CTRL. Is there a simple way to zoom in and out? here is a…

Create a Table of Contents In Excel Using VBA

The following macro not only creates a table of contents at the beginning of the workbook, but it also adds hyperlinks so that you can easily click a hyperlink to jump to a sheet. Create a Table of Contents for your workbook Sub CreateTableOfContents() ‘Step 1: Declare Variables Dim i…

Protect or Unprotect All Worksheets At Once

If you want to protect or unprotect all worksheets in Excel, you only can do each worksheet manually. Is there any way to have a Macro protect or unprotect multiple worksheets at once in Excel quickly and easily? Protect All Worksheets At Once Protect All Worksheets Use InputBox Sub ProtectAllWorksheetsWithInputbox()…