Copy values from Column B to C, stopping at column B's end

[jimayers]

Hi - I am trying to write a code that searches for any value in a cell in column B. If it has a value to copy it to the cell next to it in column C. If it is blank I want it to leave it alone. Then move on to the next cell down in B and repeat..all the way down to my last cell in B.

Here is what I have:
Sub copypaste()


Dim i As Integer
Dim LastRow As Integer 'Long?
ActiveSheet.Range.Cells("B1").Select

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For i = 1 To LastRow


If ActiveSheet.Range.Cells(i, "B") = "" Then
Selection.Offset(1, 0).Select
ElseIf ActiveSheet.Range.Cells(i, "B") <> Nothing Then
Range.Cells(i, "B").Copy
Range.Cells(i, "C").Paste
End If
Next i

End With
End Sub

Thanks for your help - Jim A

 

[My Aswer Is This]

Peter your code does the same as mine and may be quicker but I do not understand your script. I cannot see where it says "is not empty". Maybe some day you can explain how to read these type scripts.

Perhaps I'm not understanding, but couldn't you just copy them all at once?

Sub CopyBC()
  Range("B1", Range("B" & Rows.Count).End(xlUp)).Copy Destination:=Range("C1")
End Sub

 

[Peter_SSs]

I'm all good. It works. Once data is copied I am eliminating column B anyway.

You still shouldn't need to loop through each row.
Give this a try in another copy of your workbook.

Sub Copy_BC()
  With Range("C1:C" & Range("B" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace(Replace("if(len(#),#,if(len(^),^,""""))", "#", .Address), "^", .Offset(, -1).Address))
  End With
  Columns("B").Delete
End Sub

 

[Peter_SSs]

Peter your code does the same as mine and may be quicker but I do not understand your script. I cannot see where it says "is not empty". Maybe some day you can explain how to read these type scripts.

No, my original code would have over-written any existing values in column C - yours wouldn't have. At the time I posted we hadn't been told there was, or could be, values already in that column.

BTW, here's another pretty short one that I think should also do the trick.

Sub CopyBC()
  Range("B1", Range("B" & Rows.Count).End(xlUp)).Copy
  Range("C1").PasteSpecial SkipBlanks:=True
  Columns("B").Delete
End Sub

 

[My Aswer Is This]

I'm glad we have people like you here Peter and I'm sure this works. But this looks more like the Chinese language to me.

("if(len(#),#,if(len(^),^,""""))", "#", .Address), "^", .Offset(, -1).Address))

 

[Peter_SSs]

I'm glad we have people like you here Peter and I'm sure this works. But this looks more like the Chinese language to me.

Perhaps the edit in my last post makes easier reading?

 

[My Aswer Is This]

O yes this one in post 13 looks readable to a person like me.
I'm assuming script like this this one below have more to do with mathematic formulas then Vba.

("if(len(#),#,if(len(^),^,""""))", "#", .Address), "^", .Offset(, -1).Address))

Perhaps the edit in my last post makes easier reading?

 

[Peter_SSs]

I'm assuming script like this this one below have more to do with mathematic formulas then Vba.

("if(len(#),#,if(len(^),^,""""))", "#", .Address), "^", .Offset(, -1).Address))

No, that's more like putting this formula in, say, D1 and copying down

=IF(LEN(C1),C1,IF(LEN(B1),B1,""))

.. only it does the whole range at once and it does it directly in column C where there is (or may be) already some values and it does it as values directly, rather than the intermediate step of formula then convert to value.

 

[My Aswer Is This]

OK Thanks

No, that's more like putting this formula in, say, D1 and copying down

=IF(LEN(C1),C1,IF(LEN(B1),B1,""))

.. only it does the whole range at once and it does it directly in column C where there is (or may be) already some values and it does it as values directly, rather than the intermediate step of formula then convert to value.