+ Reply to Thread
Results 1 to 5 of 5

Macro to fill blank cells in column A based on non-blank cells

  1. #1
    Registered User
    Join Date
    08-24-2013
    Location
    Morgan Hill, CA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Macro to fill blank cells in column A based on non-blank cells

    I'm looking for help with creating a macro that will fill blank cells in column A with an Account number. In the attached sample, I would like the text in A6 to be copied from A7 to A54 (stopping at A54 because there's already text in A55), then copy the text in A55 from A56 to A98 (again stopping at A98 because there's already text in A99), then copy the text in A99 . . . and so on. The rows to copy from and to will change, so the macro needs to start with A6 but then only copy to the point where text is encountered and copy that new text forward. I can put the word END at the cell where I would want the macro to stop.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Macro to fill blank cells in column A based on non-blank cells

    what you are looking to have done should be fairly easily doable using standard functionality (no VBA required) - by accessing F5 > Specials > Blanks... BUT, it is not working on your sheet, because the blank cells on your worksheet aren't really behaving as blanks. so, here is a macro that mimics that functionality:

    Please Login or Register  to view this content.
    instructions for execution upon opening the file:

    insert the above code into a new Module. then, navigate to ThisWorkbook > Workbook (from General list) > Open (from Declarations list), and call the above macro, so that it looks like:

    Please Login or Register  to view this content.
    now, as soon as macros are enabled, this macro should execute.
    Last edited by icestationzbra; 03-01-2014 at 12:24 PM. Reason: Updated with "End", WorkbookOpen
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to fill blank cells in column A based on non-blank cells

    Another:

    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Macro to fill blank cells in column A based on non-blank cells

    No loop code.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-24-2013
    Location
    Morgan Hill, CA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Macro to fill blank cells in column A based on non-blank cells

    Thank you so much, this is dramatically faster. ADDITIONAL QUESTION: Now that I've worked with a few different downloaded reports, I'm finding that the macro sometimes runs well past the end of the dataset. In the attached you'll see that the macro should have stopped at row 548, but it went all the way to row 4077. I can make my download put the word END in column A at the bottom of the dataset. Can the macro be revised to stop when it encounters the word END? Ultimately, I'd like to execute this macro when I open the file.
    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)

Similar Threads

  1. [SOLVED] How to fill only blank cells based on another worksheet
    By meprad in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-26-2013, 04:13 AM
  2. Fill blank cells with data directly above that blank cell
    By gibson2503 in forum Excel General
    Replies: 3
    Last Post: 06-05-2013, 10:07 AM
  3. [SOLVED] Search and fill blank cells based on various conditions
    By johnny_tc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-24-2012, 09:40 AM
  4. Fill blank cells in column with value above
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2012, 09:46 AM
  5. how can i fill blank cells in column with abc while the right col.
    By khurram saddique in forum Excel General
    Replies: 2
    Last Post: 02-12-2005, 12:06 PM

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