Rows and Columns in VBA

The Rows property is used to reference rows in an Excel worksheet.

Rows(1)

This represents the complete first row of the active worksheet.

Rows(5)

This represents the complete fifth row.

The Columns property is used to reference columns in an Excel worksheet.

Columns(1)

This represents the complete first column, which is column A.

Columns(3)

This represents column C.

You can reference a complete row by using its row number.

Rows(2)

This references the complete second row of the active worksheet.

The row can then be formatted, cleared, deleted, or used for other operations.

You can reference a complete column by using its column number.

Columns(2)

This references the complete second column, which is column B.

The number inside Rows represents the row number.

You can format an entire row using the Rows property.

Rows(1).Font.Bold = True

This makes all cells in row 1 bold.

Rows(1).Font.Size = 14

This changes the font size of the first row.

The Columns property can also be used for formatting.

Columns(1).Font.Bold = True

This makes all cells in column A bold.

Columns(2).Font.Size = 12

This changes the font size of column B.

It is better to specify the worksheet when working with rows.

Worksheets("Students").Rows(1).Font.Bold = True

This makes the first row of the Students worksheet bold.

Worksheets("Students").Columns(1).Font.Bold = True

This makes column A of the Students worksheet bold.

Using the worksheet name helps ensure that the correct worksheet is modified.

The RowHeight property changes the height of a row.

Rows(1).RowHeight = 30

This changes the height of row 1 to 30 points.

The ColumnWidth property changes the width of a column.

Columns(1).ColumnWidth = 20

This changes the width of column A.

The AutoFit method can automatically adjust row height.

Rows(1).AutoFit

Excel automatically adjusts the height of row 1 according to its content.

The AutoFit method can also adjust column width.

Columns(1).AutoFit

Excel automatically adjusts column A according to its content.

The Select method can be used to select a row.

Rows(2).Select

This selects the complete second row.

However, in practical VBA programming, it is usually better to work directly with objects instead of selecting them unnecessarily.

A complete column can also be selected.

Columns(3).Select

This selects column C.

You can remove the contents of an entire row using ClearContents.

Rows(5).ClearContents

This removes the values and formulas from row 5 while keeping its formatting.

The same method can be used with columns.

Columns(4).ClearContents

This clears the contents of column D.

The Delete method removes a complete row.

Rows(5).Delete

Row 5 is deleted and the rows below it move upward.

A complete column can also be deleted.

Columns(4).Delete

This deletes column D and shifts the columns on the right to the left.

The Insert method can add a new row.

Rows(5).Insert

A new blank row is inserted before row 5.

You can insert a new column using the Columns property.

Columns(3).Insert

A new blank column is inserted before column C.

Rows can be processed one by one using a loop.

Dim i As Long

For i = 1 To 10

    Rows(i).Font.Bold = True

Next i

This makes the first ten rows bold.

Columns can also be processed using a loop.

Dim i As Long

For i = 1 To 5

    Columns(i).AutoFit

Next i

This automatically adjusts the width of the first five columns.

Suppose a student worksheet contains data in columns A to D. You can automatically adjust the columns:

Sub FormatStudentData()

    Columns(1).ColumnWidth = 15
    Columns(2).ColumnWidth = 25
    Columns(3).ColumnWidth = 20
    Columns(4).ColumnWidth = 15

    Rows(1).Font.Bold = True
    Rows(1).RowHeight = 25

End Sub

This can be useful for creating a clean student data sheet.

Common beginner mistakes include:

• Confusing row numbers with column numbers.
• Deleting the wrong row or column.
• Forgetting to specify the correct worksheet.
• Using Select when it is not necessary.
• Changing an entire column when only a few cells were needed.
• Using incorrect loop limits.
• Changing worksheet structure without checking existing data.

The following procedure formats a simple student worksheet.

Sub FormatStudentSheet()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Students")

    ws.Rows(1).Font.Bold = True
    ws.Rows(1).RowHeight = 25

    ws.Columns(1).ColumnWidth = 15
    ws.Columns(2).ColumnWidth = 25
    ws.Columns(3).ColumnWidth = 20
    ws.Columns(4).ColumnWidth = 15

    ws.Columns("A:D").AutoFit

    MsgBox "Worksheet Formatted Successfully"

End Sub

• Specify the worksheet when working with important data.
• Use Rows for complete row operations.
• Use Columns for complete column operations.
• Use AutoFit when appropriate.
• Be careful when using Delete.
• Avoid unnecessary Select operations.
• Use loops when processing multiple rows or columns.
• Use variables when row or column numbers are dynamic.

A typical VBA workflow is:

1. Select or reference the required worksheet.
2. Choose the required row or column.
3. Perform the required operation.
4. Use loops for multiple rows or columns.
5. Check the result before saving important data.

Dim ws As Worksheet
Dim i As Long

Set ws = ThisWorkbook.Worksheets("Students")

For i = 1 To 5

    ws.Rows(i).RowHeight = 25

Next i

ws.Columns("A:D").AutoFit

The Rows and Columns properties provide a convenient way to work with complete rows and columns in Excel VBA.

For example:

Rows(1).Font.Bold = True

makes the first row bold.

Columns(1).AutoFit

automatically adjusts the width of column A.

Rows and Columns can also be used with loops:

Dim i As Long

For i = 1 To 10

    Rows(i).RowHeight = 22

Next i

They are useful for formatting reports, preparing student records, adjusting worksheet layouts, inserting or deleting data, and automating Excel reports.

The next lesson will explain how to select and activate cells using VBA.