+ Reply to Thread
Results 1 to 8 of 8

Macro adds rows with values, not blanks

  1. #1
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Talking Macro adds rows with values, not blanks

    Hello!

    Using the modified Dave McRitchie InsertRowsAndFillFormulas macro as well as a highlight active row macro. Rows are being added, but they have values in them. Workbook attached. Anyone see how to fix this code?

    Thanks!

    Lost

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by leaning; 06-21-2011 at 11:01 AM. Reason: Solved!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Macro adds rows with values, not blanks

    Hi Learning,

    I see you have a single worksheet in this workbook. You also have 3 userforms. There is about 400 lines of code. You have event code behine the worksheet that fires each time a change happens on it.

    When I tried to open this workbook I got a debug stop message on the Unprotect line.

    My suggestion is to take the Subs that are behind the worksheet that are NOT event macros and put them into a MODULE. I see the code in the Sub you mention goes through all sheets. I'm not sure that is allowed when in code behind a single worksheet.

    Read http://www.cpearson.com/excel/Events.aspx for more background
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Macro adds rows with values, not blanks

    hi, leaning, try to comment out the following line or check attachment:
    Please Login or Register  to view this content.
    in Sub InsertRowsAndFillFormulas()
    Attached Files Attached Files
    Last edited by watersev; 06-16-2011 at 10:55 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Re: Macro adds rows with values, not blanks

    MarvinP,

    I cleaned up the attached workbook. Thanks for the help!

    Watersev: Your help fixed the problem!

    Two last things:

    1) The "data table" itself needs to be unlocked to allow user edit. And the cells below it need to be locked. If the user adds rows, those rows need to have just the A and B cells unlocked.

    2) Sometimes when the user adds rows, the formatting (borders, text alignment, don't make it to the new row. So, the user has to unprotect the document, do edits, and then reprotect. (If you look at the attached, I added a row under the "17", and the borders didn't carry over.)

    Any ideas about these?

    Lost
    Attached Files Attached Files
    Last edited by leaning; 06-16-2011 at 11:39 AM. Reason: typos

  5. #5
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Talking Adds Blank Row to Bottom of Sheet

    All,

    In case anyone needs this thread, here's what the attached workbook has:

    1) Table-like section with 2 columns for user to add data. (This section has nothing below it.)
    2) Button to add new blank row to the bottom of that section.
    3) New row code allows changing the properties (border, font, data validation, etc.) for each of the individual cells added (Columns A:G for this).
    4) Column G has a SUM formula (via VBA) and is locked.
    5) There is a named range (WONum) that looks at Col A of the section (without listing the blanks), so you can use those values for data validation in other places.
    6) There is code to highlight each row as you are working on it.
    7) The Delete Rows button can delete multiple rows selected, not just one.

    Thanks to everyone for your help on this!

    Regards,

    Lost
    Attached Files Attached Files
    Last edited by leaning; 06-20-2011 at 10:11 AM. Reason: Code change in workbook.

  6. #6
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Talking Adds Blank Row to Middle Section of Sheet

    All,

    The attached workbook has the same setup as the one above, only with this one, you can add a variable number of rows between rows. It also adds new rows to the middle of a sheet vice always adding them to the bottom (last unused row).

    This gem was almostly entirely the work of realniceguy5000!

    Good stuff! HTH!

    Regards,

    Lost
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Talking (Updated) Adds Blank Row to Middle of Sheet

    All,

    The code that highlights the active row was printing the highlight.

    Fixed in the attached!

    HTH!

    Lost
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    02-26-2010
    Location
    Chattanooga, TN
    MS-Off Ver
    Excel 2003/2007/2010/2016
    Posts
    432

    Talking (Updated) Adds Blank Row to Bottom of Sheet

    Hello!

    The workbook where a blank row gets added to the bottom of sheet and doesn't have a bottom section (as opposed to the variety that adds rows but has a footer section like the workbook above) has been updated and is attached.

    1. The code that highlights the active row was printing the highlight. (Fixed with a different code.)

    2. The data validation list was displaying blanks and duplicates. (Fixed by snb using the code in this thread: http://www.excelforum.com/excel-prog...e-formula.html! Thanks!)

    HTH!

    Respectfully,

    Lost
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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