When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

In code you could do something like:

Option Explicit
Sub testme()
With ActiveSheet
With .cells 'or a specific range: With .Range("D:D")
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With
End Sub

====
You could add that kind of code after the .pastespecial line, but before the "on
error" line.

EmB wrote:

I guess I didn't realize something in my "table"....the "blank" cells aren't
really blank. I think they are "", which might not be the same, since when I
try to select blanks, they are not selected. However, I am able to filter on
"blanks" for each column, highlight all and hit "delete" on the keyboard, and
then you suggestion works. I might try to make a macro for this.

Thank you so much about the "select blnaks" - very handy Excel tip!

"Dave Peterson" wrote:

ps.

Option Explicit
Sub testme()
Dim Wks As Worksheet
Dim NewWks As Worksheet

Set Wks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

Wks.Cells.Copy
NewWks.Range("A1").PasteSpecial Paste:=xlPasteValues

On Error Resume Next 'just in case there are no empty cells
NewWks.Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftToLeft
On Error GoTo 0

End Sub




EmB wrote:

I have a question I am trying to solve. I have a table that, for many rows,
does not have every column filled in. For example, the table would look like
this:

First Last Color Age Fruit Vegatable
John Smith Blue 15 Celery
Jane Doe 50 Orange
Jim Red Carrot
Jones 90 Apple Pepper

and I want it to look like this:
John Smith Blue 15 Celery
Jane Doe 50 Orange
Jim Red Carrot
Jones 90 Apple Pepper

Basically, if a certain column is "empty" a specific row, I want to skip it
and paste the next filled in value next to a previous filled in value. Is
there any way to do this in a Macro? I'd like to read from my "old" table to
create a "new" table.

The solution does not have to be elegant, and I can hard code in start rows
and end rows.

--

Dave Peterson
.


--

Dave Peterson