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…

Excel VBA: Change Calculation Mode To Manual

Sub WorkbookCalculationManual() With Application ‘Workbook calculation settings .Calculation = xlCalculationManual ‘Change calculation mode to manual ‘.CalculateBeforeSave = True ‘Recalculate workbook before saving, if saving a workbook takes a long time, set to False. ‘.Calculation = xlCalculationAutomatic ‘Change calculation mode to automatic ‘.Calculation = xlCalculationSemiautomatic ‘Automatic except for data tables ‘Iterative…