+ Reply to Thread
Results 1 to 24 of 24

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 2016
    Posts
    67

    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
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    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 2016
    Posts
    67

    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 2016
    Posts
    67

    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 torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    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 2016
    Posts
    67

    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 2016
    Posts
    67

    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 2016
    Posts
    67

    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 torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

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

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

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    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 2016
    Posts
    67

    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 2016
    Posts
    67

    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 torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    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 2016
    Posts
    67

    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 2016
    Posts
    67

    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 torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    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 2016
    Posts
    67

    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.

  18. #18
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2016
    Posts
    67

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

    Dear Torachan. I am studying your file very carefully. I can learn a lot from your work. I really appreciate. Your file did not work at the beginning but by studying I was able to make little corrections and now it is. I really appreciate the great job you did. Definitely I will have questions soon. I also really appreciate you attached a calendar. What a great job. Maybe you can suggest any good book with examples or good web based tutorials to learn programming like you do. You are right , I almost completely do not use objects. Since now I will. Once again, thanks for great job, I am still studying your work. Can I add a reputation if the case is not closed?
    thx a lot master !

  19. #19
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

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

    thanks for the feedback.
    below two sites that have probably given me more insight than any other- the rest comes with time, patience and a mass of reading and trial.


  20. #20
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2016
    Posts
    67

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

    Thanks a lot. I visited those sites. Today I am studying the calendar. I can not figure out where are btn1 and btn2, both are dimmed as MSForms.CommandButtons? I stopped there. If you do not mind, please share some info. I would like to understand every line in Calendar. Thx

  21. #21
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

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

    De-mystify btn1 and btn2, basically they could be any of the command button, they establish which of the command buttons is the first day of the month, then btn2 is set plus one day establishing which command button is day two, this then establishes the direction of the loop which places the caption on the relative command buttons in a pattern group according to the days of the week.Once the captions are on the buttons they can be identified and enabled, those without day caption are then greyed out and disabled.
    Thereafter the command button caption determines the value passed, the rest is repetitive,if I had time and patience I would condense the repetitive code.
    torachan.

  22. #22
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2016
    Posts
    67

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

    Dear Torachan. I really studied your work carefully. I am not experienced Forum user. How to mark it as solved ? If you do not mind, I may send you a question from time to time regarding your file.
    Thanks again a lot. F

  23. #23
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

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

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  24. #24
    Registered User
    Join Date
    05-29-2012
    Location
    Wilmingotn DE USA
    MS-Off Ver
    Excel 2016
    Posts
    67

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

    Thanks a lot. F

+ 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