PDA

View Full Version : Solved: Shift cells right?



TrippyTom
10-12-2011, 04:29 PM
Hi gang,
(please see attached sample data)

I have a very long list of addresses, and the city/state/zip are in ONE cell (which is ok) but they're falling under different columns all over the place.

Is there a quick way to shift the cells to the right so at least the city/state/zip is all in the same column?

I'm not sure of the best way to handle this. I was thinking of maybe offsetting each row by the number of blank cells in the "Address" columns, but that would require vba I think.

If there's an easier way I'd appreciate a tip. :)

Bob Phillips
10-13-2011, 01:11 AM
Public Sub ProcessData()
Dim Lastrow As Long
Dim Lastcol As Long
Dim Lastcol2 As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 1 To Lastrow 'Lastrow to 1 Step -1

Lastcol2 = .Cells(i, .Columns.Count).End(xlToLeft).Column
If Lastcol2 < Lastcol Then

.Cells(i, Lastcol2).Copy .Cells(i, Lastcol)
.Cells(i, Lastcol2).ClearContents
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

TrippyTom
10-13-2011, 09:31 AM
You are simply amazing. :bow:
Thanks so much. That's pure magic right there.

Ok, so looking at your code, here's my "guess" as to what your code does:

You count the rows and do a loop through them.
then it examines the row and if there's a blank cell, it copies the last cell with contents to the last column
then it deletes the contents of the original cell so there's no duplicate information (effectively "moving" the data).Is this correct? (marking the thread solved)

Bob Phillips
10-13-2011, 11:00 AM
Not quite on point 2. I capture the last column number of the header row, and as I loop through each data row I check that row's last data column, and if it is less than the header row's last column, then it moves it from its current position to same column as the end of the header row. Doesn't check if there is a blank, just checks column numbers.