Screenshot of my Table

I want to find a way to have a new row appear once I have filled the previous one with text. This is to use as a quote tool.

I currently have a table with rows of tasks to complete and a cost summary at the foot. I would like for this to start with one blank row for me to type the entry in and then add a new row as soon as I have done so in order to allow me to then add a new entry if needed.

I have tried a variety of things including adding a macro button to insert a row when clicked, but I would rather it be automated as the button method requires for the row to be typed in which can lead to error. I have also been trying to work with the following in VBA but am getting an overflow error. I tried to ammend this based on something I found elsewhere online. I am new to VBA and am only vaguely aware of where I am going wrong with this.

Sub AddBlankRows()
Dim iRow As Integer, iCol As Integer
Dim oRng As Range

Set oRng = Range("D6")

iRow = oRng.Row
iCol = oRng.Column

If Not Cells(iRow, iCol).Text = "" Then

    Cells(iRow + 1, iCol).EntireRow.Insert shift:=xlDown

    iRow = iRow + 2


    iRow = iRow + 1

End If

Loop While Not Cells(iRow, iCol).Text = "Description of Work"

End Sub
  • 1
    Welcome to the site. Please share with us what you have tried and where you are stuck. We are happy to help, but this is not a forum to have us do it for you. There is more information in How to Ask. – CharlieRB May 18 '17 at 16:35
  • Hello @CharlieRB, I have edited my question and hope it's a bit clearer now as to what I am trying to achieve and what I have been trying. Thanks. – Lydia W Jun 5 '17 at 17:54

It's wrong approach. This is imposible to do without VBA knowledge.

BUT you can try to use named tables (MS Excel 2007 and above only). Create table with three columns (Entry, Cost, Charge) fill first row, select this table and press CTRL+T (check the checkbox about head).

Now, you have table with filters (which are applied to table range only). When you fill some data to row under table, it will be automaticaly added to the table. And what is best, you can easily do SUM (or other formula) for whole* table column! =SUM(Table_name[Column_name])

Table name can be edited in the ribbon list which appears when is cell in the table selected.

GIF example

  • Sorry, I didn't notice that you already used named tables. So you are familiar with this feature. What you do wrong is that Excel philosophy is basicaly one column = one data type. Place tables in row and move summary above their heads. That is easiest way, how to achieve this behavior without VBA. – Lluser May 19 '17 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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