Excel 2010 Bible

Part VI: Programming Excel with VBA

896

If the active sheet contains a range named data, the following statement assigns the number of cells

in the data range to a variable named CellCount:

CellCount = Range(“data”).Count

You can also determine how many rows or columns are contained in a range. The following

expression calculates the number of columns in the currently selected range:

Selection.Columns.Count

And, of course, you can also use the Rows property to determine the number of rows in a range.

The following statement counts the number of rows in a range named data and assigns the number

to a variable named RowCount:

RowCount = Range(“data”).Rows.Count

Working with Workbooks

The examples in this section demonstrate various ways to use VBA to work with workbooks.

Saving all workbooks

The following procedure loops through all workbooks in the Workbooks collection and saves

each file that has been saved previously:

Public Sub SaveAllWorkbooks()

Dim Book As Workbook

For Each Book In Workbooks

If Book.Path <> “” Then Book.Save

Next Book

End Sub

Notice the use of the Path property. If a workbook’s Path property is empty, the file has never

been saved (it’s a new workbook). This procedure ignores such workbooks and saves only the

workbooks that have a nonempty Path property.

Saving and closing all workbooks

The following procedure loops through the Workbooks collection. The code saves and closes all

workbooks.