How to select Cells in a Worksheet

There are many ways to select cells in a worksheet. In this post we will demonstrate the most commonly used methods to select cells. Select a single cell within the worksheet and then use Ctrl + A and all cells will be selected. If the cell selected contains data then the Ctrl + A selects the region containing the data. Pressing Ctrl + A again will select the entire worksheet.

Read More »

Unhide Multiple Worksheets

Have you ever received a workbook from that has multiple hidden worksheets. The standard approach within Excel is to right click on the worksheet names and select Unhide. Then you need to select one worksheet at a time to unhide. If there are many worksheets this can take forever.

Read More »

Copy Email Items to Excel Workbook

Have you ever wanted to copy Email Items from Outlook to an Excel Workbook. Once in Excel you can then analyse the information in those emails such as the sender, subject and message details. Here is an example of a routine that you can use to copy details of a formatted email to an Excel workbook. It is started from within Outlook after selecting the Mail Items that need to be processed.

Read More »

Change Cell Formats using Ranges and Offset

The following Macro has been designed to read through a range of cells and update the Cell formats for Font, Color, Borders and Background Color. This type of Macro can be really useful in worksheet updates where inserts, updates and deletion of cells can result in a loss of formatting for the worksheet. With this Macro the formats can be re-applied with one command after all the updates are made.

Read More »

Is Workbook Open Function

The following Function has been designed to check if a workbook you are about to open is already open. If it is then it will return TRUE, if not then the Function will return FALSE and your other Macro code should then open the workbook that is required.

Read More »

Chart Exists Function

The following Function has been designed to check if a chart already exists in the open workbook. If it does exist then it will return TRUE, if not then the Function will return FALSE. Based on this function your other code can then create the chart if needed or use the existing chart.

Read More »

Prompt user to select file

The following module is an interface which prompts the user of the document to select a text file from a pop up dialog box (similar to the Open or Save As window). Once the file is selected, the pathname and document name is recorded. This pathname/filename can then be used to automatically open the text file and further coding used to import and format the data. This macro is particularly useful for reporting purposes where data is imported on a regular basis.

Read More »

Find and FindNext

For this example we have a list of transactions by date for a series of account numbers. We want to calculate the total transactions for each account number into a summary report. As there are a number of transactions for each Account Number we need to use a combination of Find and FindNext to continue searching through the whole range.

Read More »

Find as a Function

In our previous post on Find and FindNext we saw that in combination they can be used to search through large amounts of data very quickly. In many cases we need to use Find and FindNext on a worksheet. The best way to implement repeatable and automated worksheet based functions is through User Defined Function (UDF). This is just a function written in VBA code that can be used on a worksheet.

Read More »