+ Reply to Thread
Results 1 to 17 of 17

Insert Row in vba causes creation of many empty rows below . Why?

  1. #1
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Question Insert Row in vba causes creation of many empty rows below . Why?

    Dear Sirs
    I have Userform#1 (with property ShowModal=false), this form invokes Userform#2 (also with property ShowModal=false). One procedure from Userform#2 is inserting a row in a table:
    sheets("data").range("top").offset(1,0).Insert shift:=xldown, copyoriging:=xlFormatFromRightOrBelow
    When above line is executed, vba inserts n empty rows under just created row. Each time I do this procedure, the number of empty rows =n+1. Extremely strange behavior. WHY ? It used to be OK when I had only one Userform with property ShowModal=true. Maybe this is a culprit. But how to make it work with two Userforms being used simultaneously? Please help. Flowerski

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,902

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    How is Range("top") defined?

  3. #3
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    Thanks for response. Range top is not defined, there is no dim top as range or anything else. I just created a range "top" on the spreadsheet. Should I dim it as range ?

  4. #4
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    Jazzer thanks again for response. I made: dim top as range and I modified appropriate line with offset
    top.offset(1,0).Insert shift:=xldown, rest is the same.
    The code still creates n+1 empty rows after executing above statement. Still need help. Thx

  5. #5
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,832

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  6. #6
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    ok, thanks. I am at work and I have to leave in a minute. Will continue tomorrow. Thanks F

  7. #7
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    I think I just uploaded two pdf files to show my problem as requested by torachan. I do not see those files.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    Aaaa, I can see them after submitting. So line #17 was inserted and under that line there are many empty rows created by one tome procedure execution. No idea why those rows are created and why each time i execute procedure number of empty rows is bigger by one, n=n+1. Need help. Thx F

  9. #9
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,832

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    help us to help you - upload your workbook.
    torachan.

  10. #10
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,902

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    Quote Originally Posted by Flowerski View Post
    I think I just uploaded two pdf files
    Don't upload PDF files. Upload your actual Excel file.

    Range top is not defined, there is no dim top as range or anything else. I just created a range "top" on the spreadsheet.
    I didn't mean defined in the code. What is the range "top" in the spreadsheet? If you go to Name Manager, what is the range for "top"?

  11. #11
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    I think I attached with workbook as requested
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    SHort instruction how to use workbook NUOCA....
    Simply open file, click green button OPTIONS, then userform1 will show up and press green button NEW NUOCA. Then enter date (this is input thing), then userform2 will show up and please enter button START, then list will show up, choose any line (any category name) and then click yellow button NEXT. That is it. If you click gray button then you will see data listed with lots of empty rows. Why those rows are being created? Please note that both userforms have property ShowModal=false. As default that property =True. If I do not change this property I can not use two userforms simultaneously. Thx . Still need help. Thx Jazzer for suggestion.

  13. #13
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,832

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    Give the attached a run.
    Basically you have ten times toooo much code doing so little.
    You had set a table up for your data but did not use any table objects to save data this set your range down one row.
    There was no range find so the data did not know where to go.
    I have assign the actual columns to the data, your method of searching for column headers makes the code slow.
    All this could be acheived off one userform.
    torachan.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    Thanks. Let me have a look inside. I have to uf because entire project is not finished. Next to green button Enter NUOCA I am planning to place few more buttons. Anyway, appreciate a lot. I will dig in as soon as I am done with small emergency at work. Thanks for you time and energy.

  15. #15
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    Dear Torochan. Thanks for working on my workbook. Your workbook does not work. I made small correction. In sub dataentry i added "+1" to the line f=sheet2.cells(...... +1. After adding "+1" one new lines are added to the data but the lines are added to the bottom. What I had in my version, the "insert" with "offset" procedure allowed me to always add new line at the top of data , it is easy to view that way. I really would like to keep this feature. Do you think you can redo the code to add new line at the top. Why I use names of columns? Because I am still redesigning table and if I have column name than right data is stored in right column. It is helping me but I understand your comment/suggestion. Thx

  16. #16
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,832

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    I have put together a basic form that combines the elements usually incorporated within a form for data entry.
    The process covers the ability to enter new data, to be able to edit/update that data and/or to be able to delete/remove selected data.
    As your requirement it adds new data to the first row of the databody instead of the normal appending to the bottom.
    To add data first press 'NEW' this clears the form and fills the first textbox with row counter# with data form filled press 'ADD'
    To select any row for update or deletion select from the listbox.
    All the data manipulation is done using the 'Table List Object' referencing, the table expands/contracts dynamically without manual intervention.
    You will see by leaving your textboxes sequentially number with the column sequence you can populate with three or four lines of looping code instead of individually named textboxes demanding their own code line.
    Perhaps something to consider when you have a clearer idea of you requirements.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Insert Row in vba causes creation of many empty rows below . Why?

    Sir, thanks a lot. I will study all your work carefully. I am sure I can learn something. Thx again and I will let you know later how things work. Thx again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] HELP!!! How to insert 170 empty rows at every 41th row?
    By isaachoo in forum Excel General
    Replies: 11
    Last Post: 09-22-2020, 06:58 AM
  2. Replies: 0
    Last Post: 09-18-2019, 05:41 AM
  3. Insert multiple empty rows
    By mandura in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-25-2015, 07:47 AM
  4. [SOLVED] vba insert empty rows into worksheets
    By vio.coman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-17-2014, 07:25 AM
  5. [SOLVED] Insert empty/blank rows
    By Danielle22 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2013, 04:30 PM
  6. insert two empty rows below used data
    By johncena in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-01-2010, 05:49 AM
  7. insert 1 empty row after 100 rows
    By vinz20k in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2007, 03:52 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1