Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: auto insert a blank row after each record

  1. #1
    Join Date
    2nd March 2006
    Posts
    6

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th February 2005
    Posts
    175

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    2nd March 2006
    Posts
    6

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    Re: auto insert a blank row after each record

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

    VB:
    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 at 16:50.
    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

  5. #5
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd March 2006
    Posts
    6

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    Re: auto insert a blank row after each record

    do you have macros enabled?
    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

  8. #8
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    Re: auto insert a blank row after each record

    try this. Of course macros must be enabled.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by h1h; March 2nd, 2006 at 23:57.
    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

  9. #9
    Join Date
    2nd March 2006
    Posts
    6

    Re: auto insert a blank row after each record

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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    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.
    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Automatically Insert Row When Record Complete
    By scout_mo in forum EXCEL HELP
    Replies: 6
    Last Post: April 15th, 2014, 19:01
  2. Insert New Record With Vba
    By spinkung in forum Excel and/or Access Help
    Replies: 1
    Last Post: August 19th, 2007, 19:44
  3. Insert Skipped Record
    By lyambor in forum EXCEL HELP
    Replies: 8
    Last Post: January 29th, 2007, 23:49
  4. Insert Blank Row Every nth Row
    By kmar in forum EXCEL HELP
    Replies: 7
    Last Post: September 8th, 2006, 09:30
  5. Auto-create a record
    By Zzyzzyth in forum Excel and/or Access Help
    Replies: 7
    Last Post: September 1st, 2006, 06:57

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno