Help with Excel VB Script

Hi there,

I must say that I am completely VB illiterate and hence need some help with the following.

I am trying to create a bill of costs spreadsheet in Excel which I have successfully completed. I want to add the option of increasing a line at the bottom (between the final line and the Total Sum).

I was able to create a macro to add the line but had difficulty with making Excel always add the line to the bottom of the list as the list always gets bigger and hence the last line is always updated.

Does this make sense? Can anyone help with writing some simple script?

Thanks,
Petes

PS. Attached is a sample file.

You mean make it for free.....

Well.....




Like this......(Lucky sun off a .....)

I couldn't view Dnereb's code, so I don't know if this is any different...

This is not the most elegant solution, but it's reliable and simple. I'm assuming you're not going to run into 100s of lines, otherwise performance may be an issue.

VB Code:

Private Function FindLastLine() As Long
    Dim r As Long
    r = 7
    With ActiveSheet
        While .Cells(r, 1).Text <> ""
            r = r + 1
        Wend
    End With
    FindLastLine = r
End Function
 
Sub AddRow2()
    Dim r As Long
    r = FindLastLine
    Rows(r - 1).Select
    Selection.Insert shift:=xlDown
    Cells(r - 1, 1).Formula = r - 7
    Cells(r, 1).Formula = r - 6
End Sub

Excel VBA question moved to Office Development.