Prev: Finding new records in subsequent reports (XL07)
Next: Excel development tools?
From: EmB on 22 Apr 2010 14:44
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.
From: Dave Peterson on 22 Apr 2010 15:25
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

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
From: Dave Peterson on 22 Apr 2010 15:29
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
 | 
Pages: 1
Prev: Finding new records in subsequent reports (XL07)
Next: Excel development tools?