Moving around the Worksheet in VBA for Excel

Rows and Columns in VBA for Excel

Columns and Column

To select a single column  you will write:
Columns("A").Select
Notice than Columns is plural even if you are selecting only one column.

To select a set of contiguous columns you will write:
Columns("A:B").Select

A special use of Range
To select a set of non contiguous columns you will not write:
Columns("A:A,C:C,E:E").Select
but:
Range("A:A,C:C,E:E").Select

Column (no "s") will be used in a line of code like the following:
var=Activecell.Column will store the column number in a variable named var
Activecell.Column cannot be use by itself
var=Selection.Column will store the column number of the Activecell of the selection in a variable named var

See Part 4 to learn about Activecell and Selection and see Part 5 on how to use this property with a variable

Rows and Row

To select a single row  you will write:
Rows("1").Select

Notice than Rows is plural even if you are selecting only one row.

To select a set of contiguous rows you will write:
Rows("2:3").Select

A special use of Range
To select a set of non contiguous rows you will not write:
Rows("1:1,3:3,5:5").Select
but:
Range("1:1,3:3,5:5").Select

Row (no "s") will be used in a line of code like the following:
var=Activecell.Row will store the row number in a variable named var
Activecell.Row cannot be use by itself
var=Selection.Row will store the row number of the Activecell of the selection in a variable named var

See Part 4 to learn about Activecell and Selection and see Part 5 on how to use this property with a variable

EntireColumn, EntireRow

You can also select the column or the row with this:
ActiveCell.EntireColumn.Select
ActiveCell.EntireRow.Select
Range("A1").EntireColumn.Select
Range("A1").EntireRow.Select

If more than one cell is selected the following code will select all rows and columns covered by the selection:
Selection.EntireColumn.Select
Selection.EntireRow.Select

Insert

Here is the code to insert rows. The number of rows inserted depends on the number of rows that are selected when executing the "Insert" command. If only on row is selected only one row is inserted if 3 rows are selected 3 rows are added. Notice below that the words Rows and Columns are plural.

Inserting one row:
Rows("3:3").Select
Selection.Insert

Inserting 3 rows:
Rows("5:8").Select
Selection.Insert

Inserting one column:
Columns("A:A").Select
Selection.Insert

Inserting 3 columns:
Columns("A:C").Select
Selection.Insert

Hidden
To hide and unhide
Columns("A").Hidden=True
Columns("A").Hidden=False

Rows("1").Hidden=True
Rows("1").Hidden=False

Remember that when you go down a column cell by cell with Selection.Offset(1,0).Select the hidden cells gets also selected. So if you are in cell A12 and cell A13 is hidden from A12 you are going down to A13 even if it is hidden and not to the next visible cell A14.

Learn More
At your own pace
In the confort of your home and office