+ Reply to Thread
Results 1 to 7 of 7

copy current cells and fill the empty cells below it consecutively

  1. #1
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    copy current cells and fill the empty cells below it consecutively

    I have to copy and fill empty roughly 5 empty cells below each cell with data in it, is there a way to automatically copy the cell data downward to each empty cell until the next cell with data is reached? and the repeat the process for the next cell with data downward?

    here in this example, each cell with data needs to be copied down to the empty cells, apart from copy drag is there a function that can do this? because I have 330 of these data lines that need copying down..

    Screen Shot 2016-09-09 at 10.21.12 PM.png

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: copy current cells and fill the empty cells below it consecutively

    If I understand what you want to do...

    Let's assume this is your data:

    Data Range
    A
    1
    1
    2
    3
    4
    5
    6
    2
    7
    8
    9
    10
    11
    3
    12
    13
    14
    15
    End


    You'll have to mark the end of range. I've simply entered the word End to do that.

    Select the entire range A1:A15
    Press function key F5
    Click the Special button
    Select Blanks
    OK

    This will select all the empty cells within the range and cell A2 will be the active cell.

    Type this formula:

    =A1

    Then enter it using the key combination of CTRL ENTER. (Hold down the CTRL key then hit ENTER)

    The result will be:

    Data Range
    A
    1
    1
    2
    1
    3
    1
    4
    1
    5
    1
    6
    2
    7
    2
    8
    2
    9
    2
    10
    2
    11
    3
    12
    3
    13
    3
    14
    3
    15
    End


    Then you can delete the word End in A15.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: copy current cells and fill the empty cells below it consecutively

    This is GOLD !!! thankyou so much, it saved me hours.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: copy current cells and fill the empty cells below it consecutively

    You're welcome. Thanks for the feedback!

  5. #5
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: copy current cells and fill the empty cells below it consecutively

    Tony,
    I dont know what i`m doing wrong with this one, but when I follow the procedure, and type =P2 in cell P3, it copies "Variant Grams" all the way down the column to the end of the selection I made.
    Screen Shot 2016-09-10 at 10.02.15 AM.png

  6. #6
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: copy current cells and fill the empty cells below it consecutively

    It`s ok I fixed it

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: copy current cells and fill the empty cells below it consecutively

    Good deal!

+ 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. Fill empty cells..
    By Ootso in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-14-2015, 04:30 AM
  2. [SOLVED] How to copy nonconsecutive cells into another sheet consecutively
    By clprdctn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-10-2014, 04:25 PM
  3. [SOLVED] Need formula to sum range of cells consecutively across a row not duplicating used cells
    By angie18a in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-28-2013, 03:46 PM
  4. fill empty cells with text of cells above
    By Huug in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-16-2012, 01:37 PM
  5. [SOLVED] Fill Empty Cells
    By srikanthk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-30-2012, 08:13 AM
  6. [SOLVED] Fill empty cells with content of full cells above it
    By julia81 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2011, 03:57 PM
  7. [SOLVED] Fill empty cells
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2006, 11:50 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