Columns

[Guest]

Hello,

I copied a table from Word into an Excel spreadsheet (this Word table was
created by copying rows in from other tables). There was just one problem:
some of the data shifted to the right. It affects just the last 3 columns at
the right -- specifically, the last 3 columns, which are City, State, and Zip
Code.

In the Word table they look like this:

City State Zip
New York NY 11222
New York NY 11223
New York NY 11224
New York NY 11225
.... etc.

When I copied the table into Excel, this is what happened:

City State Zip (Next Column)
New York NY 11222
New York NY 11223
New York NY 11224
New York NY 11225
New York NY 11226
New York NY 11227

I have been just selecting and moving the data to the left, but there are
4,000 rows and it's taking forever.

Is there something I can do before copying the Word table into Excel that
will prevent this from happening?

Thanks,

 

[Guest]

Rosemary

Try this. Right click the worksheet view code and paste this in:-

Sub stantial()
lastrow = Range("B65536").End(xlUp).Row
For X = lastrow To 1 Step -1
If Cells(X, 2).Value = "" Then
Cells(X, 2).Select
Selection.Delete Shift:=xlToLeft
End If
Next
End Sub

This assumes your indented column is Column B. To change it obviously change
the B in the first line to the correct column but also change the 2 in
If Cells(X, 2).Value = "" Then
Cells(X, 2).Select
The correct column number (2=b, 3=c etc)

Mike

 

[Don Guillett]

Maybe??

Sub moveover()
For i = 2 To 4000
If Cells(i, 1) = "" Then Cells(i, 2).Resize(, 4).Cut Cells(i, 1)
If Cells(i, 2) = "" Then Cells(i, 3).Resize(, 3).Cut Cells(i, 2)
End Sub