Announcement

Collapse
No announcement yet.

auto insert a blank row after each record

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • wubefrub
    started a topic auto insert a blank row after each record

    auto insert a blank row after each record

    hi. i need to create a list of my friends, their personal info, and how much they have contributed to a fund. I need to have blank rows auto-inserted after each entry, before the last row (meant for the fund's total).

    i know nuts about macros and stuff and i've been going thru forums and sites for hours, and i see that most tutorials teaches the use of vba, which i hardly understand.

    I have columns for Name, Address, Status, AmountA, AmountB and AmountC.
    AmountC = AmountA-AmountB.

    The last row is meant for the Total AmountC.

    How can I auto-insert blank rows after each record, before the final row?
    Another thing is that how can I sum up the total AmountC collected without knowing how many rows there'll be eventually?

    I hope my words are clear enough, and I pray someone will be able to help real soon. Thanks in advanced!

  • Reafidy
    replied
    Re: auto insert a blank row after each record

    damo2300,

    You must start your own thread if you have questions, no matter how old the thread is. You can post a link to this one if you see it as helpful.

    Leave a comment:


  • damo2300
    replied
    Re: auto insert a blank row after each record

    Sorry to reopen such an old thread but I was searching everywhere for this answer today and was so happy to find it here (so I signed up)

    Macro works perfectly and I have added it to our business form.

    However, the cells that get filled in on our form are bordered. This macro just adds a new blank line. Is it possible for the macro to also create borders to the new line up to say column J ?

    If anyone can answer this one you have saved a lot of pain for our business!

    Thank you

    Leave a comment:


  • wubefrub
    replied
    Re: auto insert a blank row after each record

    hey guys everything works fine already! thanks for the help! you guys were great!

    Leave a comment:


  • wubefrub
    replied
    Re: auto insert a blank row after each record

    alright! it works! thanks alot!

    sorry, one qn though. in your example, you have 4 columns where i can input my numbers. how do i make it such that the rightmost column is the result of say the first numerical column, added with the 2nd numerical column, minus the 3rd numerical column.

    after this, the total will add up only the 4th column.

    e.g.

    A1 (+) A2 (-) A3 (=) A4
    20 35 10 45
    10 50 20 40
    (that auto blank row)
    Total 85
    Last edited by wubefrub; March 3rd, 2006, 01:10.

    Leave a comment:


  • h1h
    replied
    Re: auto insert a blank row after each record

    go to menue tool -- macro --- security and choose low or medium. with medium you will be asked if macros for a specific file should be enabled. with low you will not be asked but macros are allways enabled.

    Mind, I uploaded a different file above. the first one was bad version. sorry.

    Leave a comment:


  • wubefrub
    replied
    Re: auto insert a blank row after each record

    uhm...i don't think so..how do i do that?

    Leave a comment:


  • h1h
    replied
    Re: auto insert a blank row after each record

    try this. Of course macros must be enabled.
    Attached Files
    Last edited by h1h; March 3rd, 2006, 00:57.

    Leave a comment:


  • h1h
    replied
    Re: auto insert a blank row after each record

    do you have macros enabled?

    Leave a comment:


  • wubefrub
    replied
    Re: auto insert a blank row after each record

    hey guys, thanks alot for helping, but I don't think anything works. I am totally clueless. Sorry.

    One question, rbrhodes. The document you attached; do I need to export it as another format, and then view it, or will it just work immediately? I tried filling in an entry and pressing enter, but the document did not insert a blank row after that row.

    Thanks, and sorry for being clueless about this.

    Leave a comment:


  • rbrhodes
    replied
    Re: auto insert a blank row after each record

    Hi wubefrub & welcome to Ozgrid!,

    This example presumes 'Move selection after Enter' is set to 'to right'. (In Tools/Options/Edit). Thats about the only presumption.

    So if you fill in name/address/status/AmtA/AmtB and press <Enter> then it inserts a row, moves everything down and runs a total.

    Actually, if you fill in AmountB and press <Enter> it will try! I don't know what errors you expect to make, yet...

    Cheers,

    dr
    Attached Files

    Leave a comment:


  • h1h
    replied
    Re: auto insert a blank row after each record

    try this. rightclick on sheet tab. select view code and paste the below

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        r = Target.Row
        c = Target.Column
        If c <> 1 Then Exit Sub
        Application.EnableEvents = False
        NextLineValue = Cells(r + 1, c)
        If NextLineValue = "Total" Then
            Rows(r + 1).Insert
        End If
        Application.EnableEvents = True
        
    End Sub
    it assumes that the cell in col A of the total row reads "Total"
    Last edited by h1h; March 2nd, 2006, 17:50.

    Leave a comment:


  • wubefrub
    replied
    Re: auto insert a blank row after each record

    Sorry, but I guess that wasn't quite what I wanted.

    What I meant was that, after entering an entry, a new blank row would be inserted (I guess you got this part), and this new blank row is ready to take in new data. When I enter a record into this blank row, a new blank row would automatically be inserted below it. Thus, the last row (sum of AmountC) would be slowly be "pushed down" as I enter more records.

    I hope I put it down clearer now. thanks again.

    Leave a comment:


  • mattenborough
    replied
    Re: auto insert a blank row after each record

    There are probably many ways to do this however, a very simple method would be to use the Data Sort feature.

    Enter your data ( Name, Address, AmountA-C) in columns B-F. Once you have all your Data in return to COlumn A next to your first Entry. Type in the number 1. If you have a large number of entries you can Highlight the Number 1 in Column A and then highlight all the remaining Cells next to where your data appears. Click on Edit | Fill |Series and click OK. Now next to your friends names the numbers will appear from 1 - however many entries you have. These numbers will be highlighted so click on Copy. Go to the last Entry and select the row right below it and then click on Paste. Now the series of numbers will appear once next to your data and a second time next to blank rows. Now select all the data, ( With the Cursor next to the First Entry in Column A, Hold down the Shift Key and hit the END key. This will select all the data in the sheet. Next Click on Data | Sort and click OK. All the data will now be separated by a blank Row.

    Your Sum Formula will always work if you set it up a Row below your last line of data. If you add additional data be sure to do it above the Total formula and it will always work.

    Leave a comment:

Working...
X