Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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.

share|improve this answer

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.

share|improve this answer

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)

Set objRange = objWorksheet.UsedRange

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

share|improve this answer
Sorry Paul, this is not really what I need. Let's say I have a table with 5 entries (6 rows - 1 header + 5 data). I'd like to see the 7th row blank so that when a user type a new entry the drop down lists and validation happens as the user type. – Martin Dec 31 '11 at 1:05
OK, no problem, I hope someone else can help you! – Paul Dec 31 '11 at 23:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .