Loop through cells copy and paste

[Junior6202]

Hi All,

I am new to VBA and I want to performed this task:

I have two columns in excel A and B. I want to loop through column B and If cell its not Null copy the data and replace the data in the cell in column A. of course if it was only a few cells I would do it manually but I have a few thousands to go through. Thank you any help is appreciated.
[pre]
Before VBA:
column A column B
1 5
2
3 1
4 2
5


Results:
Column A Column B
5 5
2
1 1
2 2
5
[/pre]

 

[SkipVought]

Hi,

Turn on your Auto Filter.

Filter on column B, not BLANK.

Copy the data in column B.

Paste over column A.

Unfilter.

If that works, turn on your macro recorder and record for future use.

 

[Junior6202]

I initially tried that but it does not work. It give me an error that says " That command cannot be used on multiple selections" that's why I thought maybe some vba code would work.

 

[SkipVought]

okay try this

Sub CopyValues()
    Dim r As Range
    
    With ActiveSheet
        For Each r In Intersect(.UsedRange, .Columns(2))
            Select Case Trim(r.Value)
                Case ""
                Case Else
                    .Cells(r.Row, "A").Value = r.Value
            End Select
        Next
    End With
End Sub

 

[Deniall]

You are lucky you got an error.[ ] On my system (Excel 2010 / Win 7) the operation gives a result, but not the one you were expecting.[ ] The visible selected cells are pasted as if they were in a contiguous range.[ ] This is not what you are wanting, but one can easily envisage circumstances where it would be exactly what the user was wanting.

 

[Deniall]

A bit of digging has revealed …

No need to use VBA, nor even to use filtering.[ ] Select the full extent of your column B, then "copy" that.[ ] Select the top cell of your column A.[ ] Now:
Either
Right-click > PasteSpecial > PasteSpecial
or
use the shortcut Ctrl-Alt-V.

This will bring up a detailed PasteSpecial options window I have never found before.[ ] Check the "Skip blanks" box, and hit "OK".

Done.