Useful Excel VBA Range Technique

Titles made simple in VBA

It is common to create headings for data sheets in Excel VBA. There is an array technique that can make this a simple process.

Let’s say we want to label row 1 with First Name, Last Name, Gender and State in four separate columns. The simple VBA command that performs this is

Range("A1:D1") = Array("First Name", "Last Name", "Gender", "State")

This inserts the entries on the active sheet – see below.

Titles inserted

If you wanted to run it on a specific sheet you would use the sheet codename as below

Sheet1.Range("A1:D1") = Array("First Name", "Last Name", "Gender", "State")

The sheet codename is the name VBA uses in the Project window on the left of the code. See image below

Codename

Using the codename gets around the issue of users changing sheet tab name.

To change the column width to match the headings you could use the following lines of code

Range("A1:D1").EntireColumn.AutoFit

Or

Sheet1.Range("A1:D1").EntireColumn.AutoFit

Titles autofit

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.