Page 174 - Excel Progamming Weekend Crash Course

k540629 ch11.qxd 9/2/03 9:34 AM Page 149

Session 11 — Working with Columns, Rows, and Cells 149

‘ and the “number” argument specifies how many columns.

Dim i As Integer

For i = first To first + number - 1
Columns(i).NumberFormat = “$#,##0.00”
Next i

End Sub
After inserting this procedure into the VBA Editor, you cannot simply run it directly
because it requires arguments. To try it out, you must create another procedure that calls
the FormatColumnsAsCurrency procedure. This other procedure might look similar to this:

Sub Test()
FormatColumnsAsCurrency 2, 3

End Sub
The arguments passed in this example apply the format to columns B through D.
To test the program, enter your test procedure code in the same module as the
FormatColumnsAsCurrency procedure; then run it.

Adding and Deleting Rows and Columns

The Range object has the properties EntireColumn and EntireRow that allow you to work
with entire rows and columns, rather than just the portion that is contained within the
range. You can use these properties to add and delete rows and columns.
To add one or more columns or rows:

1. Define a Range object that spans the desired number of columns or rows in the
location where you want the new columns or rows located.
2. Call the Range object’s EntireColumn.Insert or EntireRow.Insert method.
When you insert columns, existing data is shifted to the right. For example, look at this
code:
Range(“B2:C2”).EntireColumn.Insert

The results are:
Two columns (B and C) are inserted.
The old column B has moved over to column D, and C has moved over to E.

When new rows are added, existing rows are shifted down in the same manner.
To delete entire rows or columns, use the EntireColumn.Delete or EntireRow.Delete
method. For example, this code deletes rows 5 through 10:
Range(“A10”).EntireRow.Delete