Table data entry - How to always have a blank row at the end of an Excel table

Question

I'm creating a simple Excel spreadsheet for table data entry (table created using the "Format as table" option -- couple of columns, there could be hundred of rows). I would like to always have the last row of the table blank and ready for data entry (otherwise I don't see the dropdowns). I am pretty sure I have seen that in the past, but I can't replicate. Any idea how I could do that? Ideally without VBA.

Answer 1

It's often the simplest method to extend the size of your table in advance for as many rows as you think you'll need. Sorting and formulas will continue to work as expected.

• Select your table
• In the Table Tools/Design menu, click Resize Table and specify a longer range.

Alternatively, the manual way is to select the last row in your table and do "Insert row below" (or use the little arrow in the bottom right cell). This could be automated using VBA if you're so inclined, although I usually find extending the table does the job just as well and makes it easier for the end-user to deal with.

Answer 2

Well, tabbing out of the the last cell in the last row in which you're entering data should automatically add a new, blank row to the table. This only works if you don't have a totals row.

Conceivably, you'd want the new, blank row to be created when you begin your editing in the last row, rather than when you complete your editing, but I haven't been able to find a way to make that happen.

Answer 3

I don't know of a way to do it without VBA, but the following is taken from The Scripting Guy blog on TechNet. You should refer to the blog post on how to use it, but it is the best way that I currently know of.

Const xlCellTypeLastCell = 11

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate

intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" &  intNewRow

objExcel.Range(strNewCell).Activate