I think that the problem is the user didn't select the correct range to start.

Select A1:A25000 (through the last used row)

(When I tried your code, I must have selected the wrong range, too--'cause it
didn't work for me. Today, I selected the correct range and it worked
fine--What did you change! <vbg)

And your code is a lot more straight forward, too!

I would add something like:

On Error Resume Next
Range("a:a").Cells.SpecialCells(xlCellTypeBlanks). EntireRow.Delete
On Error GoTo 0

To do the clean up, though.

Duke Carey wrote:

Chris -

I think my macro does just what you want. If it finds any data in a row
WITHOUT a label in column A, it moves that data upwards, in its original
column, and places it on the row of the last found label in column A

When it's done running, all you need to do is sort on column A

Haven't looked at Dave Peterson's code. His may do the very same thing

Duke

"ChrisA" wrote:

Duke:

I think you are onto something there. It doesn't run exactly as I had hoped
but I think its a start. The macro that you wrote moves all of the data from
right to left. I want to just the opposite. Wherever there is a name, I
want all of the information pertaining to that name to be moved up to the row
where the name exists. The trick is that not all of the names have the same
information. For example, one person may have some data that is in column D
where the person below them on the spreadsheet does not have any information
in that column. In a case like this I just want a blank cell to appear in
the column ofn the row of the person that does not have any information.

I just want all of the data for the person to be on the same row rather than
staggered over 3 to 6 rows.

Any ideas?

"Duke Carey" wrote:

Do this on a copy of your data

Sub CopyNames()
Dim rng As Range
Dim strName As String
Dim lngRow As Long
Dim x As Integer

Application.ScreenUpdating = False

strName = ""
lngRow = 1
For Each rng In Selection
If Len(rng.Text) 0 Then
lngRow = rng.Row
Else
For x = 1 To 4
If Len(rng.Offset(0, x).Text) 0 Then
Cells(lngRow, x + 1).Value = rng.Offset(0, x).Value
rng.Offset(0, x).Clear
End If
Next
End If
Next

End Sub

"ChrisA" wrote:

Yeah, doing it manually would take me weeks. I have a lot of data. There
must be some macro or some clever equation that could move the appropriate
data to correct location. I have about 25,000 rows of data for about 10,000
people (averaging about five rows of data per person.)

Any other ideas?

"Gordon" wrote:

ChrisA wrote:
|| Greetings to Excel Pros:
||
|| I have a worksheet that has a label in Column A. There are five more
|| columns (B, C, D, E, F) that have data in them. The label in cell
|| A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is
|| where it gets tricky. Cell C2 is John's hours per week "40". Cell
|| D3 is John's 401K deductions "$250". As you can see from this
|| example, the data pertaining to John is staggered as I go down the
|| columns. I have several people in the same spreadsheet that have
|| data staggered in this fashion. Not all of them have the same
|| information with the same spacing. Mary for example only has hourly
|| wage info and hours per week info. I want to get rid of the blank
|| cells so that all of the data pertaining to an individual is on the
|| same row as the person's name. Any ideas???
||
|| I hope this was a clear explaination.
||
|| Chris

It will be a bit laborious, but if you right-click on a blank cell, and
choose "Delete" you will get several options as to whether you want to move
cells left, right, or up and down. Go through the list doing that until you
get all the data correct! You will have to do the reverse at some points and
insert cells!

--
Gordon Burgess-Parker
Interim Systems and Management Accounting
www.gbpcomputing.co.uk




--

Dave Peterson