+ Reply to Thread
Results 1 to 8 of 8

VBA to fill in Erased Range with data in lower rows

  1. #1
    Forum Contributor
    Join Date
    05-23-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2013
    Posts
    259

    VBA to fill in Erased Range with data in lower rows

    Hello All,

    I want to add a button to my userform that will do the following:

    1. Erase the defined range (single row)

    2. If there is any entered data below this row, I want it to "move up" to fill in the (now) blank row.

    Basically I'm looking for how to write the code so that on Commandbutton4_Click() the referenced range gets erased and all entries below this range (if there are any), move up to fill in the blank row.

    The columns involved include J to CM (first row is 12, but I can adjust the range as necessary).

    Is this easily achievable?

    Remember that if there is data in the row above the deleted row, I don't want that to move/be affected. Only rows below.

    I appreciate all of your help!!!!

    -LM

  2. #2
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

    Re: VBA to fill in Erased Range with data in lower rows

    Hello,

    Code for deleting entire row associated to range is:

    Please Login or Register  to view this content.
    So above code will delete Row 18 & will shift row 19 to its place.

    Do let us know for further help

    Cheers!!
    Last edited by JBeaucaire; 06-28-2015 at 03:34 AM.

  3. #3
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

    Re: VBA to fill in Erased Range with data in lower rows

    so your code would look like this:

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    05-23-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2013
    Posts
    259

    Re: VBA to fill in Erased Range with data in lower rows

    Hi Exelliot,

    I should have specified this in my first post, but right now I have set up 200 rows worth of data (I only want users to use these pre-formatted rows. I have code that determines the row number based on where the userform initialize/show button is located). So the only rows where data will be entered (for this workbook) is rows 12-211.

    Since my code pulls the row number based on the button's location, what I need is code that first determines how far down data goes (it can do this looking solely at column J, as its a required field in the userform they use to enter data), and then move that dynamic range up to fill in any gaps. My working knowledge of vba is a hodge-podge of stuff I shouldn't know yet (for how new I am to user-from coding), and a missing knowledge of things I probably should know.

    I apologize for not giving you a more complete picture.

    If I didn't have an end-cap, your solution would indeed work beautifully (also I don't think that code would delete the button that launches the userform).

    Will it be a coding nightmare to move the data to fill in the blank row as I have described?

    Also, in the code example you gave above, what does the "Type:=8" parameter do?

  5. #5
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

    Re: VBA to fill in Erased Range with data in lower rows

    code that first determines how far down data goes:
    Please Login or Register  to view this content.
    so you can add condition & with few alteration to code i gave:
    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 06-28-2015 at 03:35 AM.

  6. #6
    Forum Contributor
    Join Date
    05-23-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2013
    Posts
    259

    Re: VBA to fill in Erased Range with data in lower rows

    Hello Excelliot!

    Okay, so here is what I did (and why it took me several days to work out).

    I wanted to avoid your solution (using it as a last resort sort of code), because I wanted the process to be automatic (the user doesn't have to enter numbers, etc.)

    After much gnashing of teeth I finally found the magical arrangement of code that does what I want, but (isn't it always something), a new problem arose that you might have some advice on.

    First, here is the code that worked:

    Please Login or Register  to view this content.
    Where rownum is the row the button was pressed in, rowplus is the current row plus 1 (which is the start of the range I wanted to copy), and rSize is the result of the XlDown code you provided minus the rownum.

    It ends up doing exactly what I described I wanted in my first post (clears the current row and copies all data below it to fill in the gap).

    Here's the new problem: many cells unmerge. Oddly enough, if I keep running the code, when there gets to be only 1 to 2 entries remaining, the cells miraculously remerge (its just if there are 3 or more entries it "copied up", the middle entries will be in umerged cells).

    I looked into this through Google and the main takeaway was that merged cells are the devil with macros haha. I'm not sure I care for the Center Across solution (although I may have to resort to it). Is there another solution you can think of that will keep the formatting, including the merged cells?
    Last edited by liquidmettle; 06-24-2015 at 08:54 PM.

  7. #7
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

    Re: VBA to fill in Erased Range with data in lower rows

    Can you share whole code of yours?

  8. #8
    Forum Contributor
    Join Date
    05-23-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2013
    Posts
    259

    Re: VBA to fill in Erased Range with data in lower rows

    Hi Excelliot,

    I've been traveling and also just getting back to working on the code. I have a potential solution to my merge problem (likely not the most efficient but it should do nicely). I just have to figure out how to make it iterative. I think this thread sort of died while I was away. Thank you for your suggestions that led me down the proper roads.

+ 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. Macro that cuts data from lower rows and pastes them in adjacent rows
    By kehl_4 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-02-2014, 02:07 PM
  2. Replies: 1
    Last Post: 02-04-2014, 01:57 PM
  3. Excel File Erased Own Data
    By highfade in forum Excel General
    Replies: 4
    Last Post: 07-27-2013, 03:47 PM
  4. Avoid data to be erased
    By pansovic in forum Excel General
    Replies: 2
    Last Post: 06-21-2012, 09:34 PM
  5. I need help. Accidentally erased my data....!!!
    By Fotis1991 in forum Excel General
    Replies: 2
    Last Post: 11-11-2011, 04:58 AM

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