Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filling a blank with tha value of a cell above it

    I don't see an attachment, but you should be able to do it without a macro:
    - Select the table.
    - Select Edit | Go To...
    - Click Special...
    - Select Blanks, then click OK.
    - All blank cells within the table will be selected.
    - Look at the box on the left hand side of the formula bar to see which is the active cell within the selection.
    - Let's say it is A2. Enter the following formula:

    =A1

    - I.e. refer to the cell above the active cell.
    - Press Ctrl+Enter.

    - If you want to get rid of the formulas:
    - Select the entire table.
    - Select Edit | Copy.
    - Select Edit | Paste Special...
    - Select the Values option and click OK.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filling a blank with tha value of a cell above it

    If you prefer a macro:

    Sub FillTheBlanks()
    Dim oCell As Range
    For Each oCell In Selection
    If oCell.Value = "" Then
    oCell.Value = oCell.Offset(-1, 0).Value
    End If
    Next oCell
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    filling a blank with tha value of a cell above it

    I have downlaoded a table from a web site and copied the results to excel, see the attached file. The cell under 1997 is empty, as with the cell under 1998 etc. I would do execute a macro whereby the value immediately under 1997 becomes 1997 and that under 1998 becomes 1998, etc. In essence if cell is empty insert the value above it.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: filling a blank with tha value of a cell above

    i am sorry enclosed is the file in the edited post

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filling a blank with tha value of a cell above

    Both of the replies I posted should do what you want.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re : great solution

    the macro worked fine

    The rest is awsome.

Posting Permissions

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