I am trying to replace all blank lines with the first data found in the
column above it. This is what I have:

column c
srp
blank
blank
blank
sgs
blank
toc
blank
This is what I need:

srp
srp
srp
srp
sgs
sgs
toc
toc


any ideas????

You can create a macro to do this without too much dificulty but if it is
more of a "one of" kind of thing here is a simple way to do it. Assuming that
you have values in A1, A5, A30, ... do the following.

In Cell A2 add the formula =A1. Now Cell A2 is correct. Copy Cell A2. Select
the entire range you want to fill in and then hit F5 (Ctrl+G, or Edit -
Goto) to bring up the Goto Dialog. Hit Special. Now select Blanks and then
Ok. All of the blank cell that you want to fill in will now be selected.
Paste the formula that you copied and you now have all the right values in
all the right places. Copy and paste special values all of column A and you
are good to go.

If you need the macro you can probably just record a macro and modify it
slightly if you want or reply back for something a little more
sophisticated...

--
HTH...

Jim Thomlinson


"srroduin" wrote:

I am trying to replace all blank lines with the first data found in the
column above it. This is what I have:

column c
srp
blank
blank
blank
sgs
blank
toc
blank
This is what I need:

srp
srp
srp
srp
sgs
sgs
toc
toc


any ideas????

My customers don't want any formulas in the cells so it has to be a macro. I
copied one from a previous post:

Dim myRange As Range
Set myRange = Worksheets("Outage").Range("C10: C" & Lr + a)

myRange.Replace What:=" ", Replacement:="", LookAt:=xlWhole,
SearchOrder:=xlByColumns
myRange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R1C[-1]"

However it pastes 1/00/00 0:00 in every blank cell in that column. The
location of the blank cells will change everytime, but will always need to
have the data of the first filled cell above it in the column.

I hope this makes sense. I much prefer formulas....but I'm not the boss. :)

Thanks for your help.
"Jim Thomlinson" wrote:

You can create a macro to do this without too much dificulty but if it is
more of a "one of" kind of thing here is a simple way to do it. Assuming that
you have values in A1, A5, A30, ... do the following.

In Cell A2 add the formula =A1. Now Cell A2 is correct. Copy Cell A2. Select
the entire range you want to fill in and then hit F5 (Ctrl+G, or Edit -
Goto) to bring up the Goto Dialog. Hit Special. Now select Blanks and then
Ok. All of the blank cell that you want to fill in will now be selected.
Paste the formula that you copied and you now have all the right values in
all the right places. Copy and paste special values all of column A and you
are good to go.

If you need the macro you can probably just record a macro and modify it
slightly if you want or reply back for something a little more
sophisticated...

--
HTH...

Jim Thomlinson


"srroduin" wrote:

I am trying to replace all blank lines with the first data found in the
column above it. This is what I have:

column c
srp
blank
blank
blank
sgs
blank
toc
blank
This is what I need:

srp
srp
srp
srp
sgs
sgs
toc
toc


any ideas????