
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.
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.
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 do your Excel skills stack up?
Test NowNext up:
- Monitoring communication sessions in Lync Server 2013
- A truly botched presentation…but Samsung are happy!
- Combine MATCH and INDEX in Excel for a powerful tool
- 4 steps to establish a SQL Server connection
- 3 settings that will increase your efficiency in Microsoft Project
- The “New” Exchange 2013 Edge Transport Server
- Ace your next presentation with lessons from these tennis pros
- Sync document properties in SharePoint and Word
- Use SCCM 2012 R2 to manage Linux machines
- Easily convert dates to Australian format in Excel
Previously
- Protecting content in Microsoft Word with ‘Restrict Editing’
- Data Quality in SQL Server 2014 for dummies (Part 2)
- Customise the weather forecast in Outlook 2013
- Manage your administration with ADAC and PowerShell
- Automatically reach your deadlines with scheduled tasks in Microsoft Project
- Data Quality in SQL Server 2014 for dummies (Part 1)
- Life has many, many stations. Having trouble getting to your next station?
- Taking the shortcuts in Windows 8
- Multi-level sorting made possible in Microsoft Word
- Spring clean your PC with Windows ‘Disk Cleanup’