How To Automate Copy & Paste....

[Burrgogi]

In col. A, I have some "stuff"... mostly text.
In Col. B, I have this:
B1: Peaches
B2: --
B3: Oranges
B4: --
B5: --
B6: Nuts & Bolts
B7: Smith & Kline
B8: --
B9: Swimming Pool
B10: --
B11: --
B12: --
B13: --
B14: --


meaning that there is nothing contained in cell A15. So Loop, 'copy & paste' action as long as there is something in col. A.

dashes '--' denote empty cells.

Is there some function or VBA routine that can automatically fill in the empty cells so that for example in B2, it will show the value just above it - which in this case is Peaches. Copy & paste works fine, but the problem is that the number of empty cells (as you can see from the example here) varies. So if you look at the last item Swimming Pool, there are 5 empty rows compared to peaches which has only 1 empty cell. I'm trying to get the end result to look like this:

B1: Peaches
B2: Peaches
B3: Oranges
B4: Oranges
B5: Oranges
B6: Nuts & Bolts
B7: Smith & Kline
B8: Smith & Kline
B9: Swimming Pool
B10: Swimming Pool
B11: Swimming Pool
B12: Swimming Pool
B13: Swimming Pool
B14: Swimming Pool

 

[Burrgogi]

OK - I tried your suggestion and still no joy. I got this result:

B1: Peaches
B2: =R[-1]C
B3: Oranges
B4: Oranges
B5: Oranges
B6: Nuts & Bolts
B7: Smith & Kline
B8: =R[-1]C
B9: Swimming Pool
B10: =R[-1]C
B11: =R[-1]C
B12: =R[-1]C
B13: =R[-1]C
B14: =R[-1]C

I should add some more information to this thread. First of all, all this text that I'm manipulating in this worksheet is actually imported from a plain text file located on my hard drive. Secondly, there's a macro that I run just before yours VoG and I'm quite sure it doesn't have any adverse affect on it. Thirdly, instead of using the import feature in Excel, I opened up the text file manually in Notepad and then copied & pasted the data into the columns and lo & behold.... your VBA routine works fine in that situation.

Hmmm.... so something in the "import" process must be messing up the routine.

 

[Burrgogi]

I found the reason why this is happening. Basically when I import the text file into col. B, it's deemed as a query in Excel. So when run a macro that shifts the cells down the empty cells are not "empty" because it's tied to a query table in Excel memory. Therefore, when the VBA routine looks for blank cells to fill, it doesn't find any and hence produces the result: =R[-1]C.

Now only if I can find a solution!