Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Unanswered: help filling in blank cells

    I'm not quite sure how to approach this and I hope my question makes sense.
    I have attached a file with (totally made up) demo data, that should give you an idea of my issue.

    I gave a vendor a flat file representing a database a while back. So they added the flat file to their system. Now their are concerns that they added the data correctly. So we asked for them to give back their version of the file, so we can compare it to the original. Their version has a very annoying format that I need help with.
    In the version I sent, each cell was filled in, even if it repeat for 1000 rows. In their version, if a cell repeated down through many rows, they did not bother to fill in the repeats. I need to fill in those blanks so I can do a side by side comparison.

    How can I programatically fill in all those blank cells?

    The concept I came up was to check if the cell was empty, IfBlank ( grab the value of the above cell). Unfortunately, I found that it gave me a nesting limitation error if I tried to go back more than (approx) 6 cells. I would settle for 10 and manually correct the exceptions.

    So I need another idea....

    I'm thinking VBA but I do Access VBA and have never really worked in Excel VBA much.

    Any help is appreciated including links to how-tos, suggestions, actual code.. etc.

    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Try this VBA:

    Code:
    Sub FillIn()
        ' selects current region (contiguous) and fills with value above
        Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        ' copies values and PasteSpecial values
        Range("A1").CurrentRegion.Value = Range("A1").CurrentRegion.Value
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    Oh ho, awesome. I owe you.

    Already run and it works like a charm

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Great. I found it very helpful over the years. I keep it in the Personal.xls file for frequent use.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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