help filling in blank cells

starkmann · #1 (**permalink**) 02-07-08, 10:35

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

shades · #2 (**permalink**) 02-07-08, 10:38

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