macro to move data to new column

[WannabeExcelQueen]

I have data that needs to be moved from column B to C and D

Basically it's set up like below, and I need cell B2 to move to C1, and B3
to move to D1 and so on. There is a space in between sets like this and some
of them have a suite and some do not. So some sets are 4 rows then a blank
space, and some are 3 rows then a blank space.

A B C D
1 First M. Last 123 Street Name
2 Suite 123
3 City, State Zip
4
5 First M. Last 123 Street Name
6 City, State Zip
7
8


What I need is for the Suite to go to C and the CityStateZip to go to D
automatically.

Is there a code or macro I can use to do this without having to move each
row and then delete all the blanks?

 

[Per Jessen]

Try this:

Option Explicit

Dim StartCell As Range
Dim c As Integer

Sub MoveData()
Set StartCell = Range("A1")
c = 1
Do
If StartCell.Offset(c, 0).Value <> "" Then
StartCell.Offset(c, 0).Cut Destination:=StartCell.Offset(0, c)
c = c + 1
Else
Set StartCell = StartCell.Offset(c + 1, 0)
c = 1
End If
Loop Until StartCell.Value = ""
End Sub

Regards

Per

 

[WannabeExcelQueen]

I tried that and it dint' work.

I have data in column a (names)

in columb B are addresses, suites, and citySTzip

I need the suite to move from B2 to C1 and the CityState to move from B3 to D1

I need to be able to do this continually and so on.

Thank you. I'm not all that familiar with the macros so if you know the
code and should I put it in the immediate window in Visual Basic or open up a
module I would need to know that too.

Thanks much.

 

[Dan R.]

Put this in a module:

Sub moveData()
Dim i As Range, lRow As Long, rng As Range
lRow = Range("B65536").End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(lRow, 1))
For Each i In rng
If i.Value <> "" Then
If Left(i.Offset(1, 1).Value, 5) = "Suite" Then
i.Offset(1, 1).Cut i.Offset(, 2)
i.Offset(2, 1).Cut i.Offset(, 3)
i.Offset(1, 0).EntireRow.Delete
i.Offset(2, 0).EntireRow.Delete
Else
i.Offset(1, 1).Cut i.Offset(, 3)
i.Offset(1, 0).EntireRow.Delete
End If
End If
Next i
End Sub