How to populate tables in Excel VBA

 Jun 30, 2014

There may be occasions where you need to populate an existing table with more data, and in such occasions, the first thing you would generally think would be "Where is the next blank row of the table?" So here is one way to find the next first blank row of a table in Excel VBA. To find the next first blank row in an Excel workbook, let’s assume you have selected a cell somewhere in the table. This cell is referred to as the 'Active Cell.' In the Excel sheet, you can then perform Ctrl+* (or Ctrl+A) to select the whole table.

How to populate tables in Excel VBA

If we now assume that we manually shift this selection down as many rows as there are in the table, we will end up with a range selected as shown in image below. All we have to do now is start typing values in the first row of this selection.

How to populate tables in Excel VBA

Now that the steps are clear in Excel, we need to mimic these steps in Excel VBA. To do this, input the following code in your VBA script and read on for an explanation of each line of code.

Activecell.CurrentRegion.Select Selection.Offset(Selection.Rows.Count,0).Select Selection.Range(“A1”) = ”Total”

1. Activecell.CurrentRegion.Select This line is equivalent to issuing a Ctrl+* in Excel, i.e., it selects the whole table that contains the Active Cell. 2. Selection.Offset(Selection.Rows.Count,0).Select This line shifts the selection down as many rows as there are in the table. To explain this line, I start with:

  • Selection.Rows.Count – this statement is of course self descriptive; it returns the number of rows in the selected range.
  • Selection.Offset(r,c) – this function shifts the Selection r rows down and c columns to the right.

Therefore 'Selection.Offset(Selection.Rows.Count,0)' means shift the selection down “Selection.Rows.Count” rows down and “0” columns to the right. The last 'Select' selects the shifted range. The last step is to actually write a value in the first cell of the selected range. 3. Selection.Range(“A1”) = ”Total” So the word “Total” will be put in cell A23 in the below image. You can see that in this line of code, that 'Range(“A1”)' here does not refer to the Activesheet.Range(“A1”), which is the real A1. Instead, it refers to the first cell of the selected range, i.e. 'Selection.' So now you can refer to other cells as Selection.Range(“B1”). Happy coding!

How to populate tables in Excel VBA

How do your Excel skills stack up?   

Test Now  

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio
top