CODEWITHSUNDEEP
vbaexcel
UserX
UserX

Reputation: 5

Excel VBA if Column B contains a value then copy that value to Column A do not overwrite existing Column A data

I have some code right now that will look at Column B and will update Column A with a predetermined value ("ANY VALUE" from my code snippet) but what I am looking for is to be able to copy what is in Column B and paste it into Column A. Here is the code I have so far:

    Sub Copy_And_Paste_Column_Values_Into_Column_1()
On Error Resume Next
    Dim ws As Worksheet
    Dim lRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row

        .Range("A1:A" & lRow).SpecialCells(xlCellTypeBlanks).Formula = "=If(B1<>"""",""ANY VALUE"","""")"
        .Range("A1:A" & lRow).Value = .Range("A1:A" & lRow).Value
    End With
End Sub

I would like to turn this:

StartTable

into this:

EndTable

Thanks in advance for the assistance!

Upvotes: 0

Views: 62

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

You can do this a little more succinctly, using SpecialCells():

Sub copy_data_to_blanks()
Dim rng As Range
Set rng = Range("A1:A3") ' Change this as necessary.
rng.Cells.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=rc[1]"
rng.Value = rng.Value ' This effectively removes the formulas, just by overwriting the range with the actual values.
End Sub

Upvotes: 1

Related Questions