Saturday, March 26, 2005

Selecting a variable range using VBA code

I often have to use VBA code to select a range of cells that I don't know the exact shape of in Excel.
Sometimes I might know the size of one of the two dimensions, Columns or Rows. Sometimes I know neither.

How I do this is using the following steps.

First I name a range in the sheet of the starting cell (usually the leftmost top cell).
To do this I click on the cell and use Insert | Name | Define. I would usually name the cell range_start or similar.

I also make sure that the data is contiguous. That is there are no blank spaces in the ranges. If this is not possible I at least do it for 1 column and 1 row (preferably the top row and the left column but can be any ones.) If not then I hope that I know the maximum dimensions of either columns or rows.

The code is fairly simple.

Application.Goto reference:="range_start"
Range(ActiveCell, ActiveCell.End(xlDown).Offset(0, 14)).Select
Selection.Copy

This piece of code will select from the starting cell down to the bottom of the range then 14 columns right (giving 15 columns in total). It will then copy this selection to the clipboard.
The main part of the code is using the Range.Select method.
This uses the syntax Range(start cell,endcell).Select
I use starting cell as the activecell, then use the activecell and a combination of Offset and End functions to locate the end cell.

There are many more complex ways to do this including selecting CurrentRegion etc. However this way is probably the simplest easiest way to do it.
The most important thing to do is not in the code at all but to ensure that the data you are working with is clean and organised properly.


Posted by: Jethro | 12:37 PM |