myITSchools

VBA Insert Delete Rows Column

To insert a row or column in Excel using VBA, you can use the Insert method of the Range object. This method allows you to insert one or more rows or columns above or to the left of a specified range. For example, the following code inserts a new row above row 5:

Range ("5:5"). Insert

The following code inserts a new column to the left of column B:

Range ("B: B"). Insert

The following code inserts three rows above row 10:

Range ("10:12"). Insert

The following code inserts two columns to the left of column D:

Range ("D: E"). Insert

You can also use the Rows or Columns objects to refer to a single or multiple rows or columns. For example, the following code inserts a new row above row 5:

Rows (5). Insert

The following code inserts a new column to the left of column B:

Columns("B"). Insert

The following code inserts three rows above row 10:

Rows ("10:12"). Insert

The following code inserts two columns to the left of column D:

Columns ("D: E"). Insert

Delete Rows or Column using VBA

To delete a row or column in VBA, you can use the Delete method of the Range object. This method allows you to delete one or more rows or columns above or to the left of a specified range. For example, the following code deletes the entire row 5:

Rows (5). Delete

The following code deletes the entire column B:

Columns(“B”). Delete

The following code deletes the rows 10 to 12:

Rows (“10:12”). Delete

The following code deletes the columns D and E:

Columns (“D: E”). Delete

You can also use the EntireRow or EntireColumn property of the Range object to delete the entire row or column of a cell or a range of cells. For example, the following code deletes the entire row of cell A1:

Range(“A1”). EntireRow.Delete

The following code deletes the entire column of cell B2:

Range(“B2”). EntireColumn.Delete

The following code deletes the entire rows of the range C3:C6:

Range (“C3:C6”). EntireRow.Delete

The following code deletes the entire columns of the range D4:E7:

Range (“D4:E7”). EntireColumn.Delete

Show or Hide Rows or column using VBA

To show or hide a row or column in VBA, you can use the Hidden property of the Range object. This property can be set to True or False to hide or show a row or column respectively. For example, the following code hides column A and row 1:

Range (“A: A”). Hidden = True

Range (“1:1”). Hidden = True

The following code shows column B and row 2:

Range (“B: B”). Hidden = False

Range (“2:2”). Hidden = False

You can also use the Rows or Columns objects to refer to a single or multiple rows or columns. For example, the following code hides rows 3 to 5 and columns C to E:

Rows (“3:5”). Hidden = True

Columns (“C: E”). Hidden = True

The following code shows rows 6 to 8 and columns F to H:

Rows (“6:8”). Hidden = False

Columns(“F:H”). Hidden = False

Row height and columns width in VBA

Row height and column width are two properties of the Range object in Excel VBA that can be used to adjust the size of cells in a worksheet. You can set or get the values of these properties using the following syntax:

Range(“range”). RowHeight = value ‘ to set the row height

Range(“range”). ColumnWidth = value ‘ to set the column width

For example,

The following code sets the row height of row 1 to 25 points and the column width of column A to 15 points:

Range (“1:1”). RowHeight = 25

Range (“A: A”). ColumnWidth = 15

Clear Method in VBA

The clear method in Excel VBA is used to clear the contents, formatting, comments, hyperlinks, or other properties of a cell or a range of cells. You can use different variants of the clear method depending on what you want to clear. For example:

  • To clear everything in a cell or a range, you can use the Range. Clear method. This will remove the values, formulas, formatting, comments, hyperlinks, and notes from the specified range. For example, the following code clears everything in the range A1:B10:

Range (“A1:B10”). Clear

  • To clear only the contents (values and formulas) in a cell or a range, you can use the Range.ClearComments method. This will leave the formatting, comments, hyperlinks, and notes intact. For example, the following code clears only the contents in the range A1:B10:

Range (“A1:B10”). ClearComments

  • To clear only the formatting (such as font, color, alignment, borders, etc.) in a cell or a range, you can use the Range.ClearFormats method. This will leave the contents, comments, hyperlinks, and notes intact. For example, the following code clears only the formatting in the range A1:B10:

Range (“A1:B10”). ClearFormats

  • To clear only the comments in a cell or a range, you can use the Range.ClearComments method. This will leave the contents, formatting, hyperlinks, and notes intact. For example, the following code clears only the comments in the range A1:B10:

Range (“A1:B10”). ClearComments

  • To clear only the hyperlinks in a cell or a range, you can use the Range.ClearHyperlinks method. This will leave the contents, formatting, comments, and notes intact. For example, the following code clears only the hyperlinks in the range A1:B10:

Range (“A1:B10”). ClearHyperlinks

  • To clear only the notes in a cell or a range, you can use the Range.ClearNotes method. This will leave the contents, formatting, comments, and hyperlinks intact. For example, the following code clears only the notes in the range A1:B10:

Range (“A1:B10”). ClearNotes

Scroll to Top