vba training london - "with" statement

The UK's most regular instructor-led training courses.
Training information: vba training london · Excel london

Resolved · Low Priority · Version Standard

"with" statement

Petrus has attended:
Excel VBA course

by - delegate Petrus [1 post] (2007 Jun 21 Thu, 09:53) Reply

what does the "with" statement do?

RE: "with" statement

by - trainer Carlos gold contributer [360 posts] (2007 Jul 2 Mon, 10:35) Edited on 2007 Jul 2 Mon, 10:49 Reply

Hi Petrus

The With Statement is very useful if you are using several statements in a section of code that apply to the same object.

It stops you having to fully qualify the object each time.

For example:

Worksheets("Accounts").Range("A1") .Font.Bold = True
Worksheets("Accounts").Range("A1") .Value = 47.5

Can be coded as below:

With Worksheets("Accounts").Range("A1")
.Font.Bold = True
.Value = 47.5
End With

Regards

Carlos

· Company Roles in Visual Basic Training
· What Is Microsoft Excel VBA and How Can It Help You Work Smarter?

Vba tip:

Count the Rows and Columns in a Selection

If you need to count the number of rows or columns in a worksheet use the following code:

Selection.Rows.Count - Returns the number of rows in the selection

Selection.Columns.Count - Returns the number of columns in the selection

Selection.CurrentRegion.Rows.Count - Returns the number of rows in the current region of the selection

View all VBA hints and tips