Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

3 Answers

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
add comment

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
add comment

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
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.