Simple Copy/Paste Question

[nburaq]

Hi Gents,
I would like to copy a range of data and paste into another column without overwriting the existing data. Normally, what I do is I copy the range of data and when I am pasting it I choose "paste special" and then click "skip blanks" this way works but in long range of data when I do this it overwrites the existing data always so maybe the empty cells are actually not empty? I don't know is there any way to paste this data with VBA or any other method? Thanks for the help and solution! PS: my scenario look like the screenshot below

 

[Fluff]

How about

Sub nburaq()
   With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("if(@<>"""",@,if(#<>"""",#,""""))", "@", .Address), "#", .Offset(, 4).Address))
   End With
End Sub

 

[nburaq]

How about

Sub nburaq()
   With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("if(@<>"""",@,if(#<>"""",#,""""))", "@", .Address), "#", .Offset(, 4).Address))
   End With
End Sub

Hi Fluff,
You do your magic again hee it works like a charm! but one question if my paste value is starting from A1 then formula should be like this Range ("A1:A & ......... end Offset(, 4) indicates column to be copied right? In case it is not column E but F then it will be Offset(, 5)? Thanks again for your help and patience!

 

[Fluff]

In case it is not column E but F then it will be Offset(, 5)?

That's right.

 

[nburaq]

That's right.

I tried the same formula but this time giving offset value as Offset(, -5) for another worksheet but it showed me debugging error in this line so can I use this for general purpose or it was only for this sample? Thanks again

.Value = Evaluate(Replace(Replace("if(@<>"""",@,if(#<>"""",#,""""))", "@", .Address), "#", .Offset(, -5).Address))

 

[Fluff]

Is that still working on col A? If so you cannot offset 5 columns to the left of col A

 

[nburaq]

Is that still working on col A? If so you cannot offset 5 columns to the left of col A

Actually this time ranges were different to be copied and pasted here how I modified to another scenario

Sub nburaq()
   With Range("G2:G" & Range("H" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("if(@<>"""",@,if(#<>"""",#,""""))", "@", .Address), "#", .Offset(, -7).Address))
   End With
End Sub

 

[Fluff]

As column G is column 7 you cannot go 7 columns to the left as that would be col 0

 

[nburaq]

As column G is column 7 you cannot go 7 columns to the left as that would be col 0

Does it mean Offset value always start from Column A as "0"?

 

[Fluff]

No Col A is col 1 not 0.
Offset( ,-1) is one column to the left of the col it's working on, so for col G that would mean col F