Unhide All Rows and Columns

This simple macro automatically unhides all rows and columns for you. VBA Code Sub UnhideAll() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub How This Macro Works In this macro, we call on the Columns collection and the Rows collection of the worksheet. Each collection has properties that dictate where…

Insert Blank Rows Between Existing Rows In Excel VBA

Sometimes, you may need to insert blank rows between each of the existing rows into your Worksheet. Although blank rows are generally bothersome, in some situations, the final formatted version of your report requires them to separate data. This simple macro will help you to do this. If you don’t…

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…