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.

"WannabeExcelQueen" wrote:

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?

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

--
Dan

On Dec 14, 8:53 am, WannabeExcelQueen
wrote:
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.



"WannabeExcelQueen" wrote:
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?- Hide quoted text -

- Show quoted text -