Making modifications to an Excel Table with VBA (Inserting, Removing, Clearing)

Learn how to modify an existing Excel Table to Insert and Remove rows and columns, and Clear the data from a Table.

In this 16 minute video we cover:

• INSERTING Rows
  • To insert above row 4 Tbl.ListRows.Add Position:=4
  • To insert at bottom of Table Tbl.ListRows.Add
  • Use the parameter AlwaysInsert:=True (to make sure Excel inserts new rows under the Table instead of overwriting empty rows)
  • Use a For Loop to insert multiple rows
• INSERTING Columns
  • To insert to left of column 2 Tbl.ListColumns.Add Position:=2
  • To insert at right of Table Tbl.ListColumns.Add
  • Use a For Loop to insert multiple columns
  • NOTE: There is no parameter AlwaysInsert for columns (unlike rows)
• REMOVING Rows and Columns
  • To delete row 1 Tbl.ListRows(1).Delete
  • To delete rows 1-8 Tbl.DataBodyRange.Rows("1:8").Delete
  • To delete column 3 Tbl.ListColumns(3).Delete
  • To delete columns B and C Tbl.Range.Columns("B:C").Delete
• CLEARING data from a Table
  • Why it's a good idea to check for Tbl.ListRows.Count >= 1...
  • ... instead of just using Tbl.DataBodyRange.Delete

We also take a look at a simple yet important concept (near the start of the video):

• Calling a Sub-routine from another Sub-routine
• In this lesson every time we want to reset the Play Area worksheet, we just call a simple Sub-routine "Insert_Table()"
• This means we do not need to copy and paste identical code for clearing the worksheet in multiple Sub-routines - we just use one line to call the Insert_Table() sub-routine:
  • Call Insert_Table()
• The reason why this is useful is that you can write a Sub that performs a set of often repeated actions, and use that as a building block for more complex code, without having to repeatedly write that same code everywhere you need those action

And... we briefly look at how to jump around the VBA Editor:

• Use View > Definition (shortcut: Shift + F2) to jump to the definition of a Sub-routine or variable or constant
• Use View > Last Position (shortcut: Ctrl + Shift + F2) to jump back to the last position you were in (and you can press this multiple times)

Write your comments and feedback below

• If you have questions or comments about this lesson, write it in the section below.
• Alternatively you can email the instructor Victor at [email protected]