Shift data to left
 

Hi all. I have a worksheet that is basically a form that users fill out.
Column A has projects listed down the column. Columns B through CA have
names of people assigned to projects.

So in A1 is ProjectA. In B1 the name Bob, In C1 the name Bill, in D1 the
name Joe, etc..
Columns B through CA are pick lists created from DataValidation. In the
datavalidation list, I have code that, when a name is removed from the
list, the code removes that name from the front table. So if Bob is removed
from the list, the code clears the contents of cell B1.

This leaves a blank cell, followed by populated cells, and each row will be
different. My question is this - is there a way to add to the code so that
when a name is removed, all cells shift to the left filling in the blank
cells? It can be one macro that will examine the entire sheet (or a
specified range) and shift everything to the left appropriateply?

Thank you!!

Shift data to left
 

Here is a line of code that should work for you...

ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks).D elete xlToLeft

HTH

"Jim Thomlinson" wrote:

You can do this without a macro if you want. Highlight the entire sheet. Hit
F5. Hit Special Cells - Select Blanks. Now all of the blank cells on the
page will be highlighted. Right Click and choose Delete - Shift Left.

If you want a macro to do this just record your actions and ...

HTH

"Steph" wrote:

Hi all. I have a worksheet that is basically a form that users fill out.
Column A has projects listed down the column. Columns B through CA have
names of people assigned to projects.

So in A1 is ProjectA. In B1 the name Bob, In C1 the name Bill, in D1 the
name Joe, etc..
Columns B through CA are pick lists created from DataValidation. In the
datavalidation list, I have code that, when a name is removed from the
list, the code removes that name from the front table. So if Bob is removed
from the list, the code clears the contents of cell B1.

This leaves a blank cell, followed by populated cells, and each row will be
different. My question is this - is there a way to add to the code so that
when a name is removed, all cells shift to the left filling in the blank
cells? It can be one macro that will examine the entire sheet (or a
specified range) and shift everything to the left appropriateply?

Thank you!!

Shift data to left
 

Hi Jim. But won't "deleting" the blank cells shift the data validation over
too? So before the deletion, datavalidation was in columns B through CA.
If I delete, say, 7 cells, won't the data validation now only be in B
through AT? And if this is done enough, soon there won't be any cells left
with datavalidation in them.


"Jim Thomlinson" wrote in message
...
You can do this without a macro if you want. Highlight the entire sheet.
Hit
F5. Hit Special Cells - Select Blanks. Now all of the blank cells on the
page will be highlighted. Right Click and choose Delete - Shift Left.

If you want a macro to do this just record your actions and ...

HTH

"Steph" wrote:

Hi all. I have a worksheet that is basically a form that users fill
out.
Column A has projects listed down the column. Columns B through CA have
names of people assigned to projects.

So in A1 is ProjectA. In B1 the name Bob, In C1 the name Bill, in D1
the
name Joe, etc..
Columns B through CA are pick lists created from DataValidation. In
the
datavalidation list, I have code that, when a name is removed from the
list, the code removes that name from the front table. So if Bob is
removed
from the list, the code clears the contents of cell B1.

This leaves a blank cell, followed by populated cells, and each row will
be
different. My question is this - is there a way to add to the code so
that
when a name is removed, all cells shift to the left filling in the blank
cells? It can be one macro that will examine the entire sheet (or a
specified range) and shift everything to the left appropriateply?

Thank you!!

Shift data to left
 

Copy the cells in column B and paste them into the remainder of the
spreadsheet. (pastespecial - Validation). That will put the validation
back...

HTH

Sorry I took so long to get back to you... Meetings... :)

"Steph" wrote:

Hi Jim. But won't "deleting" the blank cells shift the data validation over
too? So before the deletion, datavalidation was in columns B through CA.
If I delete, say, 7 cells, won't the data validation now only be in B
through AT? And if this is done enough, soon there won't be any cells left
with datavalidation in them.


"Jim Thomlinson" wrote in message
...
You can do this without a macro if you want. Highlight the entire sheet.
Hit
F5. Hit Special Cells - Select Blanks. Now all of the blank cells on the
page will be highlighted. Right Click and choose Delete - Shift Left.

If you want a macro to do this just record your actions and ...

HTH

"Steph" wrote:

Hi all. I have a worksheet that is basically a form that users fill
out.
Column A has projects listed down the column. Columns B through CA have
names of people assigned to projects.

So in A1 is ProjectA. In B1 the name Bob, In C1 the name Bill, in D1
the
name Joe, etc..
Columns B through CA are pick lists created from DataValidation. In
the
datavalidation list, I have code that, when a name is removed from the
list, the code removes that name from the front table. So if Bob is
removed
from the list, the code clears the contents of cell B1.

This leaves a blank cell, followed by populated cells, and each row will
be
different. My question is this - is there a way to add to the code so
that
when a name is removed, all cells shift to the left filling in the blank
cells? It can be one macro that will examine the entire sheet (or a
specified range) and shift everything to the left appropriateply?

Thank you!!

Shift data to left
 

No problem Jim. Thanks so much for your help. This is a great solution.
I'll use the special cells to get rid of the blanks, then reinstate the data
validation over the entire range. Thanks again!!


"Jim Thomlinson" wrote in message
...
Copy the cells in column B and paste them into the remainder of the
spreadsheet. (pastespecial - Validation). That will put the validation
back...

HTH

Sorry I took so long to get back to you... Meetings... :)

"Steph" wrote:

Hi Jim. But won't "deleting" the blank cells shift the data validation
over
too? So before the deletion, datavalidation was in columns B through
CA.
If I delete, say, 7 cells, won't the data validation now only be in B
through AT? And if this is done enough, soon there won't be any cells
left
with datavalidation in them.


"Jim Thomlinson" wrote in
message
...
You can do this without a macro if you want. Highlight the entire
sheet.
Hit
F5. Hit Special Cells - Select Blanks. Now all of the blank cells on
the
page will be highlighted. Right Click and choose Delete - Shift Left.

If you want a macro to do this just record your actions and ...

HTH

"Steph" wrote:

Hi all. I have a worksheet that is basically a form that users fill
out.
Column A has projects listed down the column. Columns B through CA
have
names of people assigned to projects.

So in A1 is ProjectA. In B1 the name Bob, In C1 the name Bill, in D1
the
name Joe, etc..
Columns B through CA are pick lists created from DataValidation. In
the
datavalidation list, I have code that, when a name is removed from
the
list, the code removes that name from the front table. So if Bob is
removed
from the list, the code clears the contents of cell B1.

This leaves a blank cell, followed by populated cells, and each row
will
be
different. My question is this - is there a way to add to the code
so
that
when a name is removed, all cells shift to the left filling in the
blank
cells? It can be one macro that will examine the entire sheet (or a
specified range) and shift everything to the left appropriateply?

Thank you!!