Merging two columns; only overwriting blank cells

Welcome to the board!

Try this:

Select the whole range of column C.
Press control+G (goto)
Press alt+S (special)
Press k (blanks)
Press enter (ok)
Press the equals sign
Press the left arrow on the keyboard
Press control+enter

The effect is that all cells in column C that are blank, are filled with the value in column B... After, you will have formulas in there so you may want to copy and paste values on column C.

Hope that helps!

Tai

Try this with a copy of your sheet

taigovinda said:

Welcome to the board!

Try this:

Select the whole range of column C.
Press control+G (goto)
Press alt+S (special)
Press k (blanks)
Press enter (ok)
Press the equals sign
Press the left arrow on the keyboard
Press control+enter

The effect is that all cells in column C that are blank, are filled with the value in column B... After, you will have formulas in there so you may want to copy and paste values on column C.

Hope that helps!

Tai

Click to expand...

Thank you so much! The solution worked perfectly.

I am very impressed with this site. I saw in the other posts I read that the posted were responded to quickly; and my post was responded to very quickly. Once again thank you, and I am very impressed with the site.

I apologize, I spoke a little too soon. The first solution does combine the columns but as was posted the actual data in the cells are functions and I cannot copy and paste that data since it is just the functions.

I am now working on the macro solution and when I run the macro I get an error stating "No cells were found" and the debug function takes me to the line: .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]"

Any help would be greatly appreciated. Thank you!

VoG said:

Try this with a copy of your sheet

Code:

Sub MergeBC()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B1:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]"
    .Value = .Value
End With
Columns("C").Delete
End Sub

Click to expand...

I apologize, I spoke a little too soon. The first solution does combine the columns but as was posted the actual data in the cells are functions and I cannot copy and paste that data since it is just the functions.

I am now working on the macro solution and when I run the macro I get an error stating "No cells were found" and the debug function takes me to the line: .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]"

Any help would be greatly appreciated. Thank you!

Are the values in column B truly blank or spaces?

What for example does this entered in a spare cell return

=CODE(B2)

I got it to work now, thank you.

I may not have been clear in the description but we were confusing columns B and C. When I swapped the two columns in Excel and then ran the macro it worked great.

Thank you!

For future reference... when you have formulas in some cells, and you want to copy them somewhere else, you can get rid of the formulas and leave only the values. Here is how you do it:

Select a range of cells, some or all of which have formulas you want to get rid of.
Right-click, copy.
Right-click, paste special.
Press V for values (or click values).
Press enter.
Now you have values, not formulas. You can paste the values without changing your selection, thus overwriting the formulas. Or you can paste the values to the new location, leaving the formulas in the old location. Either way.

That's what I meant to note before with the below... However, if there is a solution from me and another from VoG, I am going to use VoG's solution pretty much every time!

taigovinda said:

The effect is that all cells in column C that are blank, are filled with the value in column B... After, you will have formulas in there so you may want to copy and paste values on column C.

Click to expand...

Thank you so much for the clarification. I did not know about the Paste Special being able to copy the values and not the formulas.