Resources on VBA Excel @ ExcelOptimize.Com - Part 3
Currently Browsing: VBA Excel
Posted by Andrian in VBA Excel on 03 10th, 2009 | No Comments

Moving a range

You move a range by cutting it to the Clipboard and then pasting it in another area. If you record your actions while performing a move operation, the macro recorder generates code like the following: Sub MoveRange()Range(“A1:C6”).SelectSelection.CutRange(“A10”).SelectActiveSheet.PasteEnd Sub As...
Read More
Posted by Andrian in VBA Excel on 03 9th, 2009 | No Comments

Selecting a row or column

The following procedure demonstrates how to select the column that contains the active cell. It uses the EntireColumn property, which returns a Range object that consists of a full column: Sub SelectColumn()ActiveCell.EntireColumn.SelectEnd Sub As you may expect, VBA also offers an EntireRow property,...
Read More
Posted by Andrian in VBA Excel on 03 8th, 2009 | No Comments

Selecting to the end of a row or column

You’re probably in the habit of using key combinations such as Ctrl+Shift+Right Arrow and Ctrl+Shift+Down Arrow to select a range that consists of everything from the active cell to the end of a row or a column. Not surprisingly, you can write macros that perform these types of selections.You can use...
Read More
Posted by Andrian in VBA Excel on 03 7th, 2009 | No Comments

Copying a variable-sized range

In many cases, you need to copy a range of cells but don’t know the exact row and column dimensions. For example, you might have a workbook that tracks weekly sales. The number of rows changes as you add new data.Figure 14-1 shows a range on a worksheet. This range consists of several rows, and the...
Read More
Posted by Andrian in VBA Excel on 03 6th, 2009 | No Comments

Copying a range

Copying a range ranks right up there as one of the most favorite Excel activities of all time. When you turn on the macro recorder and copy a range from A1:A5 to B1:B5, you get this VBA macro: Sub CopyRange()Range(“A1:A5”).SelectSelection.CopyRange(“B1”).SelectActiveSheet.PasteApplication.CutCopyMode...
Read More
Posted by Andrian in VBA Excel on 03 5th, 2009 | No Comments

Using a UserForm as a progress indicator

One of the most common Excel programming questions I hear is “How can I make a UserForm display the progress of a lengthy macro?”Answer: Use a UserForm to create an attractive progress indicator, as shown in Figure 18-9. Such a use of dialog boxes does, however, require a few tricks — which I’m...
Read More
Posted by Andrian in VBA Excel on 03 4th, 2009 | No Comments

Variable Type

Computers store different types of data in different ways. The way a number is stored is quite different from the way text, or a character string, is stored. Different categories of numbers are also stored in  differentways. An integer (a whole number with no decimals) is stored differently from...
Read More
Posted by Andrian in VBA Excel on 03 4th, 2009 | No Comments

Displaying a chart in a dialog box

If you need to display a chart in a UserForm, you find that Excel doesn’t provide any direct way to do so. Therefore, you need to get creative. This section describes a technique that lets you display one or more charts in a UserForm.Figure 18-12 shows an example, which displays three charts. The UserForm...
Read More
 Page 3 of 3 « 1  2  3