Merging two columns; only overwriting blank cells

[ryan.maughan]

I have a project I am working on for work. It involves 3 columns: Column A is the person's name, Column B is the computer name of the user submitted by the user, and Column C is the computer name of the user as provided from a report. Some users left Column B blank so we ran the report in Column C to try to fill in the blank cells. I need to merge columns B and C. I want to merge the columns so that column C is the "master" column and column B only fills in the blank cells of column C.

I have read through about a dozen posts about merging columns but I could not find one where one column is a "master" column that is not overwritten unless there are blank cells.

Any help would be greatly appreciated.

 

[taigovinda]

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

 

[VoG]

Try this with a copy of your sheet

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

 

[ryan.maughan]

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

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.

 

[ryan.maughan]

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!

 

[ryan.maughan]

Try this with a copy of your sheet

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

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]

Are the values in column B truly blank or spaces?

What for example does this entered in a spare cell return

=CODE(B2)

 

[ryan.maughan]

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!

 

[taigovinda]

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!

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.

 

[ryan.maughan]

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.