Getting Rid of Blank Cells

[Guest]

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

 

[Gordon]

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!

 

[Guest]

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?

 

[Guest]

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

 

[Gord Dibben]

Chris

Try selecting columns B through F and F5>Special>Blanks>OK

Now Edit>Delete>Shift cells up.

Should get what you need.


Gord Dibben Excel MVP

 

[Guest]

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?

 

[Dave Peterson]

Try this against a copy of your worksheet--it destroys the original data.

Option Explicit
Sub testme01()
Dim wks As Worksheet
Dim TopCell As Range
Dim BotCell As Range
Dim myRng As Range

Dim LastRow As Long


Set wks = Worksheets("sheet1")
With wks
Set myRng = .UsedRange 'just try to reset last used cell.
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set TopCell = .Range("a1")
Do
If IsEmpty(TopCell.Offset(1, 0)) = False Then
Set BotCell = TopCell
Else
'botcell is one up from the next name
Set BotCell = TopCell.End(xlDown).Offset(-1, 0)
If BotCell.Row = .Rows.Count - 1 Then
Set BotCell = .Cells(LastRow, "A")
End If
End If

If BotCell.Row = TopCell.Row Then
'do nothing--a single row group
Else
Set myRng = Nothing
On Error Resume Next
With .Range(TopCell, BotCell)
Set myRng = .Offset(0, 1).Resize(.Rows.Count - 1, 3) _
.Cells.SpecialCells(xlCellTypeBlanks)
End With
On Error GoTo 0

If myRng Is Nothing Then
'no gaps--do nothing
Else
myRng.FormulaR1C1 = "=if(r[+1]c="""",na(),r[+1]c)"
With TopCell.EntireRow
.Value = .Value
End With
End If
End If

Set TopCell = BotCell.Offset(1, 0)

If TopCell.Row > LastRow Then
'we're done
Exit Do
End If
Loop

'now clean up column A
On Error Resume Next
.Range("a:a").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

'now clean the na's
.Cells.Replace what:="#n/a", replacement:="", _
lookat:=xlWhole, MatchCase:=False

End With

End Sub

This is the important line:

Set myRng = .Offset(0, 1).Resize(.Rows.Count - 1, 3) _
.Cells.SpecialCells(xlCellTypeBlanks)

It finds a group, then looks at all the rows in that group except the last. If
you need more than 3 columns, just change that 3 to something else.

It fills the empty cells in that range with formulas that look at what's
below--except for that last row in the group.

 

[Guest]

Gord - That's ingenious!!

Chris

Try selecting columns B through F and F5>Special>Blanks>OK

Now Edit>Delete>Shift cells up.

Should get what you need.


Gord Dibben Excel MVP

 

[Dave Peterson]

That's a very good solution, but I don't think it works for the OP's problem.

This doesn't keep the data "grouped" correctly. Depending on how the data
looks, you could be sliding data for the last person into the first person's
group.

 

[Guest]

Hmmm. That had excaped me Dave. Good catch.

That's a very good solution, but I don't think it works for the OP's problem.

This doesn't keep the data "grouped" correctly. Depending on how the data
looks, you could be sliding data for the last person into the first person's
group.

 

[Guest]

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

 

[Dave Peterson]

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.