Data Shifting Question

[XYZ]

Hello,

I have several spreadsheets in the following format: Col A contains
names and Cols B through H contain data. So each row contains data for
a name in Col A. The problem is that some of the data rows contain only
data in Col B. Or Cols B and C. Or cols B and C and D, etc. The data
always starts in Col B and is in each subsequent column until there is
no more; there are no blank cells between cells containing data in any
row. I need to "right justify" all data so that the last data point in a
row is always in Col H, the second to last always in Col G, etc. Some
of my spreadsheets contain thousands of rows so doing this by hand is
not an option. Thanks in advance!

Cathy

 

[Dave Peterson]

I'd use a macro:

Option Explicit
Sub testme()

Application.Screenupdating = false

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Dim NumberOfEntries As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
NumberOfEntries _
= Application.CountA(.Range(.Cells(iRow, "B"), .Cells(iRow, "H")))
If NumberOfEntries = 0 _
Or NumberOfEntries = 7 Then ' b-h = 7
'do nothing
Else
.Cells(iRow, "B").Resize(1, NumberOfEntries).Cut _
Destination:=.Cells(iRow, "H") _
.Offset(0, 1 - NumberOfEntries)
End If
Next iRow
End With

Application.screenupdating = true

End Sub

Fix the worksheet name (I used sheet1).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm