Excel 2019 Bible

Part VI: Automating Excel

And, of course, you can 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:

Sub CloseAllWorkbooks()

Dim Book As Workbook

For Each Book In Workbooks

If Book.Name <> ThisWorkbook.Name Then

Book.Close SaveChanges:=True

End If

Next Book

ThisWorkbook.Close SaveChanges:=True

End Sub

The procedure uses an If statement within the For-Next loop to determine whether the

workbook is the one that contains the code. This is necessary because closing the workbook

that contains the procedure would end the code, and subsequent workbooks would not be

affected.