Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Fill Assistance (2003)

    Greetings.

    I have a need to copy/fill some data. I know how to do this in the regular method. However, a couple of the columns are rather lengthly.

    I have attached an example file with an explaination.

    The data is copied from a pivot table (and this will happen every time I update the data). as the pivot table is grouping, it does not display the values for any of the cells that are of the same value.

    Need to structure down through the column, and when a blank cell is found, the cell above needs to be copied down. The end of the column of data is the end, it does not need to copy down.


    Regards.
    Brad
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy Fill Assistance (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 19-Mar-08 09:32. added PS)</P>Select A2579 (or whatever the range)
    Edit - goto- [special]- blanks [ok]
    In the active cell type (no quotes): "+"
    and select the cell above the active
    Hold <cntrl> while pressing <enter>

    And all the blanks will be filled with the values from the cell above it

    Steve
    PS
    In a macro you can use the single Line of code:
    <pre>Range(Range("A2"), Cells(Cells.Rows.Count, 4).End(xlUp)). _
    SpecialCells(xlCellTypeBlanks). _
    FormulaR1C1 = "=+R[-1]C"</pre>


  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Fill Assistance (2003)

    Ok, that "Almost" gets it.

    BTW, that is cool.... I am guessing that because I selected the enter area as a range, it took the last cell of Col A and B and copied down. Do not need, as that skews the outcome.

    I guess I can do each col. not a bad thing I guess.. I will play around with this


    thanks,
    BRad

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy Fill Assistance (2003)

    I am not sure I understand. Neither method I proposed needs to do it column by Column. Both methods are identical and works to fill in the blanks in the selected region

    Steve

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Fill Assistance (2003)

    Steve,
    No, no all worked fine. I had selected the entire range from A1 to D581. This caused the remaining cells of col A B and C to fill after the last data fill cell.

    I played around and realized that I need to select each col range using the control/click method.


    I had not seen the edited post. I shall try that as well....

    Thanks,
    Brad

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy Fill Assistance (2003)

    Note the Macro assumes that Col D will be filled in to the bottom of the list (which is the way the pivot would be setup) so that neither A, B, or C will have an entry further down the rows than D.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •