Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Fill in the blanks (2003)

    I recently received two spreadsheet (files) with the request to extract data from one based on data in the other. The common fields in both sheets hold a 7 digit number, but the number is not repeated for each row, rather as a header. I need to automate the process of filling down each number in the sheet holding the data to be extracted for linking purposes with data in the second sheet. I do this manually now and the rest of the process I do in Access as I am not skilled in Excel. Of course the data and format of the sheet will change each month so I cannot just record a macro. Also, the number of rows that are blank are inconsistent so I need a macro that will recognize a change in the data and then fill down to the next change in data. Can this be done?
    Thanks
    chuck

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

    Re: Fill in the blanks (2003)

    A relatively simple method:
    - Select a range in the column with the numbers and blanks.
    - Select Edit | Go To...
    - Click Blanks, then OK.
    - Say the active cell in the selection is A2.
    - Enter the formula =A1 (in general, the formula should refer to the cell above the active cell).
    - Confirm with Ctrl+Enter.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill in the blanks (2003)

    Without seeing an example of what you are starting with and what you want you results to look like I'll take a stab that you are starting with some thing like this:
    <table border 1><td>data</td><td>stuff</td><td> </td><td>stuff</td><td> </td><td>stuff</td><td> </td><td>stuff</td><td>data2</td><td>more stuff</td><td> </td><td>more stuff</td><td> </td><td>more stuff</td><td> </td><td>more stuff</td></table>

    and want to finish with this:

    <table border 1><td>data</td><td>stuff</td><td>data</td><td>stuff</td><td>data</td><td>stuff</td><td>data</td><td>stuff</td><td>data2</td><td>more stuff</td><td>data2</td><td>more stuff</td><td>data2</td><td>more stuff</td><td>data2</td><td>more stuff</td></table>


    Here is a simple macro that will do the job.

    Just highlight the column (not the entire column, only the cell you want to fill) and run:

    Sub FillInTheBlanks()

    With Selection
    Selection.SpecialCells(xlCellTypeBlanks).Select.Fo rmulaR1C1 = "=R[-1]C"
    Selection.FormulaR1C1 = "=R[-1]C"
    End With

    End Sub

    or the non-macro version

    highlight the data, press F5, click Special, choose "Blanks", OK , type =, press the up arrow, press Ctrl+Enter

  4. #4
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill in the blanks (2003)

    Gentleman, thank you for your response. When I do this manually I select a range and use ctrl D and it copies over the occasional non-blank cell that does not have a number. (Something I failed to mention in my post)

    I have attached a sample.
    Thanks
    chuck

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

    Re: Fill in the blanks (2003)

    Neither I nor mbarron suggested that you should use Ctrl+D. It is not appropriate for your situation.

    Which columns do you want to fill?

    PS For the future, try to prevent such an abomination!

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill in the blanks (2003)

    I have plucked the ctrl and D keys from my keyboard.

    I want to fill Column A to capture all the data related to it in the other columns.
    Thanks
    chuck

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

    Re: Fill in the blanks (2003)

    Can the (Pittsburgh) entries be overwritten with the entry above it?

  8. #8
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill in the blanks (2003)

    Yes, otherwise your first suggestion would have worked fine. Except for the column header all cells must be populated with a COST_CENTER number.

    Other cells in column A are populated with other cities and also the months. This data is irrelevant as well.
    Thanks
    chuck

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

    Re: Fill in the blanks (2003)

    For next time, please try to provide the relevant information from the start. Your subject is misleading, because you don't want to fill only blanks, and from your description in the first post, I couldn't have guessed that non-blanks were to be overwritten.

    You want all cells in column A except for the column header to be populated with a cost center. I have no idea what to put in A2:A4, however. The following macro will fill cells starting at row 5:
    <code>
    Sub FillNotOnlyBlanks()
    Dim lngRow As Long
    Dim lngFirstRow As Long
    Dim lngLastRow As Long
    Dim lngDataRow As Long

    lngFirstRow = 5 ' Dunno what to do with rows 2-4.
    lngLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    For lngRow = lngLastRow To lngFirstRow Step -1
    If Range("B" & lngRow) = "" Then
    lngDataRow = Range("B" & lngRow).End(xlUp).Row
    Range("A" & lngRow) = Range("A" & lngDataRow)
    End If
    Next lngRow
    End Sub</code>

  10. #10
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill in the blanks (2003)

    I apologize for the poor communication.

    Not to test your patience but I would really appreciate an explanation of this code. I really could not imagine how one would determine the last row. So how does "lngLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row" work?
    Thanks
    chuck

  11. #11
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill in the blanks (2003)

    It is a search that starts at the bottom of the sheet and works its way up (SearchDirection:=xlPrevious), by row (SearchOrder:=xlByRows) until it finds a row with something (What:="*") in a cells

  12. #12
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill in the blanks (2003)

    It never occurred to me to start at the bottom.

    Your help is greatly appreciated.
    Thanks
    chuck

  13. #13
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill in the blanks (2003)

    Hans, the sub works beautifully. Thanks again.
    Thanks
    chuck

Posting Permissions

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