if then question

R

Roger

I have two columns one(A) with continuous data from top to bottom, the other
(B) with simular data only dispersed randomly from top to bottom. What I
would like to do is replace the data in column A with the contents of column
B, only when there is data in the cell in column B, if there is no data I
want the data in column A left the way it is.
thanks in advance for the help.
Roger
 
D

Don Guillett

One way.
Sub replaceifdata()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b1:b" & lr)
If Len(Trim(c)) > 0 Then c.Offset(, -1) = c
Next
End Sub
 
R

Rick Rothstein \(MVP - VB\)

I have two columns one(A) with continuous data from top to bottom, the
other
(B) with simular data only dispersed randomly from top to bottom. What I
would like to do is replace the data in column A with the contents of
column
B, only when there is data in the cell in column B, if there is no data I
want the data in column A left the way it is.
thanks in advance for the help.
Click to expand...

You can do that directly... Copy the range you want from column B, click the
matching start cell in column A, then select Paste Special from the Edit
menu item... on the Paste Special dialog box, select Values from the Paste
section, None from the Operation section and check mark the Skip Blanks
box... click OK and you are done.

Rick
 
R

Rick Rothstein \(MVP - VB\)

Unless a blank in col B is NOT really a blank

I am newly returned to Excel after a long absence, so I am rusty. Can you
please tell me what you are referring to here? Thank you.

Rick
 
D

Don Guillett

Test both with one of the cells in col B that has been contaminated by
touching the space bar so that is not really blank.
 
R

Rick Rothstein \(MVP - VB\)

Test both with one of the cells in col B that has been contaminated
by touching the space bar so that is not really blank.
Click to expand...

Okay, I see what you were saying. If I smart, I would have guessed that was
what you meant by the Trim function call you made in your VBA code. Thanks
for getting back to me on this.

Rick
 
Top