VBA for moving data

[imported_unknown]

I got a spdsht with imported names and addresses. The name appears in B1 and the corresponding address appears in B2. The same pattern continues for whatever number of names there are in the file. The address needs to be moved up to C1 for each record.I think I can probably write a 'Loop' that will move them all individually, but was wondering if there is a better way. (perhaps code that will get the program to take the data on every other row and move it)

Any help will be much appreciated. Thanks.

 

[PaddyD]

Hi - welcome to the board!

No VBA needed:

enter

=IF(MOD(ROW(),2),B2,"")

in C1 and copy down. see the example...

Book1
	A	B	C	D
1		Name1	Address1
2		Address1
3		Name2	Address2
4		Address2
5		Name3	Address3
6		Address3
7		Name4	Address4
8		Address4
9		Name5	Address5
10		Address5
11
12
Sheet1

paddy

 

[Andrew Poulsom]

Here is a VBA method, which has the advantage of deleting the address rows:

Sub Test()
    Dim Rng As Range
    Dim x As Long
    Set Rng = Range("B1:B" & Range("B65536").End(xlUp).Row)
    x = 1
    Do
        If IsEmpty(Rng.Cells(x, 1)) Then Exit Sub
        Rng.Cells(x, 2).Value = Rng.Cells(x, 1).Offset(1, 0).Value
        Rng.Cells(x, 1).Offset(1, 0).EntireRow.Delete
        x = x + 1
    Loop
End Sub