Create a small table from a large table by skipping empty columns

[EmB]

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]

It looks like you're just sliding everything to the left -- eliminating those
empty cells.

If that's true, try recording the macro when
you select the range to fix (it won't hurt if you include too many rows!)
Hit F5 (edit|goto)
Click special
check Blanks
and hit ok

Now just the empty cells should be selected.
Edit|delete|shift cells left

========
But are you sure you want to do this???

You'll be losing the nice layout (each field in its own column).

If you don't want to destroy the original data,
Create a new worksheet
copy the data over from the old worksheet (as values)
and do that routine

 

[Dave Peterson]

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]

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]

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")
.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.